convert string to date format

Database

convert string to date format

Hi
How to convert the below date in string to date format.. I try the cast function but it does not
work...

July 22, 2007

Thanks,
Mohan
19 REPLIES
Teradata Employee

Re: convert string to date format

Hello,

For character dates, you need to specify the format as well.

Examples:

SELECT CAST('July 22, 2008' AS DATE FORMAT 'MMMMBDD,BYYYY');

SELECT CAST ('January 1992' AS DATE FORMAT 'MMMMBYYYY');

Please note, the output format of the date (if using SQL Assistant) will be same as that of OS (in my case it is 07/22/2008 and 01/01/1992), though in BTEQ it will be formated with respect to the format string specified ('July 22, 2008' and 'January 1992').

Regards,

Adeel
Teradata Employee

Re: convert string to date format

Select cast('July 22, 2007' as date format 'M4BDD,BY4');

Note that 'July 4, 2007' would NOT be valid with this format, though 'July 04, 2007' would be. Teradata does not have any format specifier that allows for a single-digit day of month.
N/A

Re: convert string to date format

Hi,

I am using this old post because I have exactly a situation mentioned in the last post. In my table I a have a VARCHAR(50) column where date is coming as May 8, 2012 and January 17, 2012.

How can I cast this column.

Regards,

ADP.

N/A

Re: convert string to date format

If the format is always correct and the only problem is the single digit day you need to do some CASE/SUBSTRING:

CASE 
WHEN x LIKE '% _,%'
THEN SUBSTRING(x FROM 1 FOR POSITION(',' IN x) - 2) || '0' || SUBSTRING(x FROM POSITION(',' IN x) - 1)
ELSE x
END (DATE, FORMAT 'mmmmBdd,Byyyy')

Dieter

Teradata Employee

Re: convert string to date format

Hi,

I have similar question. I need to convert a varchar like this 8/12/2012 into DATE format. I am getting an error message that this is not a valid date. 

I appreciate your help.

Re: convert string to date format

SELECT '8/12/2012' COL1, CAST('0'||COL1 AS DATE FORMAT 'DD-MM-YYYY')

Teradata Employee

Re: convert string to date format

Thank you for your response. This worked well when the date is like '8/17/2012', however I also have dates like '8/7/2012' or '10/7/2012'. I.e. each of the day or month can be one or two digits in my input. Is there a more general way to address this? If not how can I use this method to add '0' to the month?

Many thanks again for the hlep.

Re: convert string to date format

You can check the length of the date string and convert it accordingly...

SELECT '8/12/2012' AS COL1
, CASE WHEN CHAR_LENGTH(COL1) = 9 THEN CAST('0'||COL1 AS DATE FORMAT 'DD-MM-YYYY')
ELSE CAST(COL1 AS DATE FORMAT 'DD-MM-YYYY')
END

Teradata Employee

Re: convert string to date format

But this still would't work for some dates, e.g. '10/2/2012'