All Forums Database
mohan705 2 posts Joined 05/08
09 May 2008
convert varchar to date

Hi ,Below sql giving invalid time stamp .12/31/2007 This is in varchar format 'mm/dd/yyyy'.I need to convert varchar to date PERIOD =======12/31/2007 SEL cast( cast(BUSINESS_DATE as TIMESTAMP(0) format 'dd-mm-yyyy') AS DATE FORMAT 'YYYY-MM-DD') from PERIOD /Thanks in advanceMohan

dnoeth 4628 posts Joined 11/04
09 May 2008

Hi Mohan,did you submit that query using SQL Assistant?This is using the windows locale definition of a date, you have to modify that using:Tools - Options - General: Display dates in this formatIf this isn't a problem related to SQL Assistant, then you have to re-phrase your question, because i didn't catch it...Dieter

Dieter

Sunar 59 posts Joined 02/08
09 May 2008

Hi Mohan,I was able to run successfully the below query:SEL cast( cast(current_date as TIMESTAMP(0) format 'dd-mm-yyyy') AS DATE FORMAT 'YYYY-MM-DD');Current Date------------ 2008-05-09I don't see any problem in that.

Sunar 59 posts Joined 02/08
10 May 2008

Hi Mohan,sorry yaar. Plz ignore my pervious reply.Please store/change the date format to yyyymmdd then do the further processing.I think it will work. because teradata stores the date in yyyymmdd format.Please try and let me know too.

rs131112 19 posts Joined 03/08
12 May 2008

Sounds like business_date is a varchar column, containing a date in the format of 'mm/dd/yyyy'. If so, then I think this would give you what you want (a date data type for this value):sel business_date (date, format 'mm/dd/yyyy')from ...

Nitin_Jain 2 posts Joined 02/12
29 Feb 2012

I want to cast varchar to timestamp. The below statement is working fine.

Select cast('02/14/2012 10:00:00 PM' as timestamp format 'MM/DD/YYYYBHH:MI:SSBT')

 

But if I remove 0 from 02/14/2012, it gives error "Invalid timestamp."

Select cast('2/14/2012 10:00:00 PM' as timestamp format 'MM/DD/YYYYBHH:MI:SSBT')

Nitin

Stefans 38 posts Joined 02/12
29 Feb 2012

The format is 'MM/DD/YYYYBHH:MI:SSBT'.Hence '0' is needed.

Stalin

ulrich 816 posts Joined 09/09
01 Mar 2012

check the documentation - snapshot attached.

You can not specify M/D/YYYY. You need to specify MM/DD/YYYY and in this case day and month need to be at least 2 digest.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Nitin_Jain 2 posts Joined 02/12
01 Mar 2012

I found the solution

"

Select '2/14/2012 11:00:00 PM' ts,  

CAST (

CASE WHEN CHAR_LENGTH(ts) = 21 AND CHAR_LENGTH(TRIM(SUBSTR(ts,1,10))) = 9

                THEN '0'|| TRIM(SUBSTR(ts,1,10)) || ' ' || SUBSTR(ts,11,22)

WHEN CHAR_LENGTH(ts) = 21 AND CHAR_LENGTH(TRIM(SUBSTR(ts,1,10))) = 10 AND CHAR_LENGTH(TRIM(SUBSTR(ts,12,21))) = 10

                 THEN TRIM(SUBSTR(ts,1,11)) || ' 0' || SUBSTR(ts,12,21)

WHEN CHAR_LENGTH(ts) = 20

                THEN '0'|| TRIM(SUBSTR(ts,1,10)) || ' 0' || SUBSTR(ts,11,20)

ELSE ts

END

                                AS TIMESTAMP(6) FORMAT 'MM/DD/YYYYBHH:MI:SSBT');

 

"

 

Nitin

You must sign in to leave a comment.