convert varchar to timestamp

UDA

convert varchar to timestamp

I have a column in source strdate1 as VARCHAR(23) and the value being '20090310181010'.
I am unable to insert this record into target table column whose data type is timestamp(0) using this query within the insert statement of course:-

select cast (StrDate1 as TIMESTAMP(0)) from table

OR

select cast ('20090310181010' as TIMESTAMP(0))

it says invalid timestamp.
Please help
13 REPLIES
Enthusiast

Re: convert varchar to timestamp

Hi,

Try the following query:

SELECT CAST( SUBSTR('20090310181010',1,4) || '-' || SUBSTR('20090310181010',5,2) || '-' || SUBSTR('20090310181010',7,2) || ' ' || SUBSTR('20090310181010',9,2) || ':' || SUBSTR('20090310181010',11,2) || ':' || SUBSTR('20090310181010',13,2) AS TIMESTAMP(0) ) AS STARTDATE

you can replace '20090310181010' value with your column name which is varchar(23).

Regards,
Balamurugan
Teradata Employee

Re: convert varchar to timestamp

Hello,

I believe life is not that difficult! :)

SELECT CAST ('20090310181010' AS TIMESTAMP(0) FORMAT 'YYYYMMDDHHMISS');

Regards,

Adeel
Senior Apprentice

Re: convert varchar to timestamp

Or just add a FORMAT:

select cast ('20090310181010' as TIMESTAMP(0) FORMAT 'yyyymmddhhmiss')

Dieter
Enthusiast

Re: convert varchar to timestamp

Hi,

I have a varchar column with values in the following format. 2012-09-25 00:49:59 

I am trying to cast this value into timestamp using the above suggestions but was unsuccessful. Can anyone help here.

Thankx in advance,

Irfan

Enthusiast

Re: convert varchar to timestamp

Try select cast ('2012-09-25 00:49:59' as timestamp(0) format 'yyyy-mm-ddBhh:mi:ss')

'B' is used to represent the space in the format string!

Re: convert varchar to timestamp

Hi,

I am using TPT to load my table.

I am facing an error in converting varchar to TimeStamp(0).

The segment code which is giving the error is :

STEP LOAD_DATA_INTO_TABLE

(

   APPLY ('INSERT INTO '||@TargetTable||'(

          :IDNT_TICK,

    :MSG_ID,

    :MSG_ID2,

    :MSG_ID_SERV,

    :SPLIT_ROW_NUM,

    :COMP_REIN_NUMR_FACT,

    :NUMR_FACT

);')

   TO OPERATOR (LOAD_OPERATOR [1])

   SELECT * FROM OPERATOR ( FILE_READER [1] );

);

);

IDNT_TICK is the column of data type Timestamp(0) which is defined as varchar(14) in the TPT.

Kindly let me know if there are any fixes.

Regards,

Krishna.

Enthusiast

Re: convert varchar to timestamp

I have a col with data like: 23/11/2015 10:10:00. I want to convert it to Timestamp but it's giving error: Invalid timestamp.

SEL  CAST(ab.Dt AS TIMESTAMP(0) FORMAT 'MM/DD/YYYYBHH:MI:SS.S(6)')

FROM PDP_TMP.Nov ab

Could any one tell the possible solution ?

Senior Apprentice

Re: convert varchar to timestamp

Hint:

23/11/  doesn't match MM/DD/ :-)

CAST(ab.Dt AS TIMESTAMP(0) FORMAT 'DD/MM/YYYYBHH:MI:SS')
Enthusiast

Re: convert varchar to timestamp

select cast('11/12/2013 00:00:00' as timestamp(0) format 'dd/mm/yyyybhh:mi:ss') as req_time;

The above query works fine. But my data has dates like the below too, which produces error.

select cast('6/8/2013 00:00:00' as timestamp(0) format 'dd/mm/yyyybhh:mi:ss') as req_time;

I think, 6/8/2013 does not match with dd/mm/yyyy, and hence the invalid timestamp error pops up. The varchar field which I am trying to cast to timestamp(0) has dates like: '11/12/2013 00:00:00' as well as '6/8/2013 00:00:00'.

Any possible solutions?

Thanks,

-Shardul