convert varchar to date

Database

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 advance
Mohan
8 REPLIES
Senior Apprentice

Re: convert varchar to date

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 format

If this isn't a problem related to SQL Assistant, then you have to re-phrase your question, because i didn't catch it...

Dieter
Enthusiast

Re: convert varchar to date

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-09
I don't see any problem in that.

Enthusiast

Re: convert varchar to date

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.

Enthusiast

Re: convert varchar to date

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 ...

Re: convert varchar to date

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

Enthusiast

Re: convert varchar to date

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

Supporter

Re: convert varchar to date

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.

Re: convert varchar to date

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