convert string to timestamp

Database

convert string to timestamp

My string looks like 

05-MAR-12 01.33.34.000000000 PM

I need to convert this to timestamp (in teradata), I have tried:

 cast(create_date as TIMESTAMP(0) FORMAT 'dd-mm-yybhh.mi.ss')

plus some other combinations, but everytime i get "Invalid Timestamp" Error.

Any suggestions? 

2 REPLIES

Re: convert string to timestamp

Teradata supports timestamp up to 6 decimals, your timestamp is 9 decimals (!!).

If you want to get a TS(0) you should try to get rid of the fractional digits:

 BTEQ -- Enter your SQL request or BTEQ command:

select cast('05-MAR-12 01.33.34 PM' as timestamp(0) format 'DD-MMM-YYbhh.mi.ssbT');

 *** Query completed. One row found. One column returned.

 *** Total elapsed time was 1 second.

'05-MAR-12 01.33.34 PM'

-----------------------

  05-Mar-12 01.33.34 PM

HTH

Cheers.

Carlos.

Re: convert string to timestamp

Sorry,

pressed the key too soon:

If you have your timestamp strings in a fixed-length format, you can do something like:

SELECT CAST(SUBSTR(THE_TS_STRING,1,18) ||

            SUBSTR(THE_TS_STRING, 29,3) AS TIMESTAMP(0) FORMAT 'DD-MMM-YYbhh.mi.ssbT') THE_TIMESTAMP

       FROM (SELECT '05-mar-12 01.33.34.000000000 pm' THE_TS_STRING) PRE;

 *** Query completed. One row found. One column returned.

 *** Total elapsed time was 1 second.

        THE_TIMESTAMP

---------------------

05-Mar-12 01.33.34 PM

HTH

Cheers.

Carlos.