All Forums Database
mohan705 2 posts Joined 05/08
31 Jul 2008
convert string to date format

HiHow to convert the below date in string to date format.. I try the cast function but it does not work...July 22, 2007Thanks,Mohan

Adeel Chaudhry 773 posts Joined 04/08
31 Jul 2008

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

-- If you are stuck at something .... consider it an opportunity to think anew.

Fred 1096 posts Joined 08/04
31 Jul 2008

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.

abin 11 posts Joined 04/11
08 Jun 2012

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.

 

dnoeth 4628 posts Joined 11/04
09 Jun 2012

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

Dieter

Arash 4 posts Joined 04/11
03 Oct 2012

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.
 

Qaisar Kiani 337 posts Joined 11/05
03 Oct 2012
SELECT '8/12/2012' COL1, CAST('0'||COL1 AS DATE FORMAT 'DD-MM-YYYY')
Arash 4 posts Joined 04/11
03 Oct 2012

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.
 
 
 

Qaisar Kiani 337 posts Joined 11/05
03 Oct 2012

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
Arash 4 posts Joined 04/11
04 Oct 2012

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

ulrich 816 posts Joined 09/09
04 Oct 2012

see my last comment on http://forums.teradata.com/forum/general/a-wish-list-for-future-versions-of-teradata

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Qaisar Kiani 337 posts Joined 11/05
04 Oct 2012
SELECT '8/12/2012' AS COL1
	, CAST(CASE WHEN CHAR_LENGTH(SUBSTRING(COL1 FROM 1 FOR POSITION('/' IN COL1)-1))=1 THEN '0'||SUBSTRING(COL1 FROM 1 FOR POSITION('/' IN COL1)-1) ELSE SUBSTRING(COL1 FROM 1 FOR POSITION('/' IN COL1)-1) END || '/' 
		|| CASE WHEN CHAR_LENGTH(SUBSTRING(SUBSTRING(COL1 FROM POSITION('/' IN COL1)+1 FOR CHAR_LENGTH(COL1)-POSITION('/' IN COL1)) FROM 1 FOR POSITION('/' IN SUBSTRING(COL1 FROM POSITION('/' IN COL1)+1 FOR CHAR_LENGTH(COL1)-POSITION('/' IN COL1)))-1))=1 THEN '0'||SUBSTRING(SUBSTRING(COL1 FROM POSITION('/' IN COL1)+1 FOR CHAR_LENGTH(COL1)-POSITION('/' IN COL1)) FROM 1 FOR POSITION('/' IN SUBSTRING(COL1 FROM POSITION('/' IN COL1)+1 FOR CHAR_LENGTH(COL1)-POSITION('/' IN COL1)))-1) ELSE SUBSTRING(SUBSTRING(COL1 FROM POSITION('/' IN COL1)+1 FOR CHAR_LENGTH(COL1)-POSITION('/' IN COL1)) FROM 1 FOR POSITION('/' IN SUBSTRING(COL1 FROM POSITION('/' IN COL1)+1 FOR CHAR_LENGTH(COL1)-POSITION('/' IN COL1)))-1) END 
		|| '/' || SUBSTRING(SUBSTRING(COL1 FROM POSITION('/' IN COL1)+1 FOR CHAR_LENGTH(COL1)-POSITION('/' IN COL1)) FROM POSITION('/' IN SUBSTRING(COL1 FROM POSITION('/' IN COL1)+1 FOR CHAR_LENGTH(COL1)-POSITION('/' IN COL1)))+1 FOR CHAR_LENGTH(COL1)-POSITION('/' IN SUBSTRING(COL1 FROM POSITION('/' IN COL1)+1 FOR CHAR_LENGTH(COL1)-POSITION('/' IN COL1)))) AS DATE FORMAT 'DD-MM-YYYY')
Arash 4 posts Joined 04/11
04 Oct 2012

Many thanks for all the help Teradata!
It solved my problem.
 

usczeus 2 posts Joined 04/13
29 Apr 2013

I'm trying to convert dates like this "Apr 7, 2013  8:28 PM" to date format, any suggestions?

Harpreet Singh 101 posts Joined 10/11
30 Apr 2013

Use as below 

SELECT 'Apr 3, 2013 8:28 PM' AS COL1     , case when position( ',' in (substring(COL1 FROM POSITION(' ' IN COL1)+1 for 3) )) =2 then 
SUBSTRING(COL1 FROM 1 FOR POSITION(' ' IN COL1)-1)||' 0'|| SUBSTRING(COL1 FROM POSITION(' ' IN COL1)+1) else COL1 end as datecorect,
substring(datecorect FROM POSITION(':' IN datecorect)-4) ,substring(datecorect FROM POSITION(':' IN datecorect)-3) ,substring(datecorect FROM  1 for POSITION(':' IN datecorect)-3) ,
case when position( ' ' in (substring(datecorect FROM POSITION(':' IN datecorect)-3) )) =2 then 
SUBSTRING(datecorect FROM 1 FOR POSITION(':' IN datecorect)-3)||' 0'|| SUBSTRING(datecorect FROM POSITION(':' IN datecorect)-1) else datecorect end as timecorect
,timecorect (timestamp, format 'MMMBDDBBYYYYBHH:MIBT')

 

usczeus 2 posts Joined 04/13
30 Apr 2013

Worked like a charm Harpreet!  Thank you!

You must sign in to leave a comment.