All Forums UDA
sbadnikar 4 posts Joined 12/08
12 Mar 2009
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

Balamurugan B 81 posts Joined 09/07
12 Mar 2009

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 STARTDATEyou can replace '20090310181010' value with your column name which is varchar(23).Regards,Balamurugan

Regards,
Balamurugan

Adeel Chaudhry 773 posts Joined 04/08
12 Mar 2009

Hello,I believe life is not that difficult! :)SELECT CAST ('20090310181010' AS TIMESTAMP(0) FORMAT 'YYYYMMDDHHMISS');Regards,Adeel

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

dnoeth 4628 posts Joined 11/04
12 Mar 2009

Or just add a FORMAT:select cast ('20090310181010' as TIMESTAMP(0) FORMAT 'yyyymmddhhmiss')Dieter

Dieter

irfan098 10 posts Joined 07/11
25 Sep 2012

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
 

Qaisar Kiani 337 posts Joined 11/05
25 Sep 2012

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!

KrishnaKulkarni 2 posts Joined 10/12
17 Oct 2012

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.

frozenshine 7 posts Joined 03/15
09 Mar 2016

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 ?

ank

dnoeth 4628 posts Joined 11/04
09 Mar 2016

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

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

 

Dieter

sk8s3i 35 posts Joined 06/13
30 Mar 2016
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

-Thanks Shardul

sk8s3i 35 posts Joined 06/13
30 Mar 2016

Hi All,
Need help with the above issue.

-Thanks Shardul

dnoeth 4628 posts Joined 11/04
30 Mar 2016

There was a similar thread a few weeks ago:
http://forums.teradata.com/forum/database/converting-string-to-date-using-regexp-getting-error-3798

Dieter

sk8s3i 35 posts Joined 06/13
31 Mar 2016

Thanks Dieter!
Faced more issues with it.
Here is what I was able to finally do with my data to get it correct. Would really appreciate if you could tell me any better ways.
The date field here contains dates like:
23/12/2013
2/9/2013
12/8/2014
8/11/2010

CASE 
	WHEN LENGTH(STRTOK(Effective_Date,'/',2)) = 1 AND LENGTH(STRTOK(Effective_Date,'/',1)) = 2 THEN REGEXP_REPLACE(Effective_Date, '([0-9])/', '0\1/',1,2,'c') 
	WHEN LENGTH(STRTOK(Effective_Date,'/',2)) = 2 AND LENGTH(STRTOK(Effective_Date,'/',1)) = 1 THEN REGEXP_REPLACE(Effective_Date, '([0-9])/', '0\1/',1,1,'c')
	WHEN LENGTH(STRTOK(Effective_Date,'/',2)) = 1 AND LENGTH(STRTOK(Effective_Date,'/',1)) = 1 THEN REGEXP_REPLACE(Effective_Date, '([0-9])/([0-9])', '0\1/0\2') 
	ELSE Effective_Date
END AS Effective_Date

 

 

-Thanks Shardul

dnoeth 4628 posts Joined 11/04
31 Mar 2016

What's your TD release? Did you try the SQL from the other thread?
 
TD15: REGEXP_REPLACE('2/3/2013', '\b([0-9])\b', '0\1')

TD14: REGEXP_REPLACE('2/3/2013','\b([0-9])\b', '0\1', 1, 0, 'c') 

 

Dieter

You must sign in to leave a comment.