Trying to convert a varchar to timestamps(6) in select query

General
Enthusiast

Trying to convert a varchar to timestamps(6) in select query

Though there are similar topics on casting varchar as timestamps, nothing helped my situation. Here's my problem:

 

Source Table: Table_A

Column Type : varchar(250)

Example Data: 7/29/2016 11:37:47:967

 

Target Table: Table_B

Column Type: timestamp(6)

Example Data: 5/30/2018 13:36:13.000000

 

I need to select from Table A and insert the data into Table B. This column is creating an issue since the source varchar column has only  3 values for microseconds and also the format is a little different at the end. I tried using cast as timestamp(6) and also tried to use to_timestamp function but nothing helped. 

Could anyone in the community help out and point me in the right direction please?

10 REPLIES
Junior Contributor

Re: Trying to convert a varchar to timestamps(6) in select query

The issue is not 3 vs. 6 fractional digity, it's the single digit month, Teradata doesn't allow it.

 

You can apply a RegEx to add a leading zero to any single digit:

RegExp_Replace('7/29/2016 11:37:47:967', '\b([0-9])\b', '0\1')

And then you can safely cast:

To_Timestamp(RegExp_Replace('7/29/2016 11:37:47:967', '\b([0-9])\b', '0\1'), 'mm/dd/yyyy hh:mi:ss:s(f)')

 

Enthusiast

Re: Trying to convert a varchar to timestamps(6) in select query

Thank you for your response. The regex part is working as expected, however, when I try the function "to_timestamp" it still throws an error saying "(9134) Unknown character S in the format string".

Enthusiast

Re: Trying to convert a varchar to timestamps(6) in select query

I did some more research on this and finally got this to work:

SyntaxEditor Code Snippet

select cast(RegExp_Replace(Regexp_Replace('7/29/2016 11:37:47:967',':','.',1,3),'\b([0-9])\b', '0\1') as timestamp(6) format 'MM/DD/YYYYbHH:MI:SS.S(6)') from Table_A

 However, when I replace the date value with the column name it throws an error:
select cast(RegExp_Replace(Regexp_Replace(column_name,':','.',1,3),'\b([0-9])\b', '0\1') as timestamp(6) format 'MM/DD/YYYYbHH:MI:SS.S(6)') from Table_A

 

Error is: "Invalid Timestamp"

 

Not sure how to get past this.

Highlighted
Junior Contributor

Re: Trying to convert a varchar to timestamps(6) in select query

TO_TIMESTAMP uses Oracle format strings, I used the wrong format, sorry. It must be 'mm/dd/yyyy hh:mi:ss:ff6' instead of Teradata's 'MM/DD/YYYYbHH:MI:SS.S(6)'

Enthusiast

Re: Trying to convert a varchar to timestamps(6) in select query

That helped, however, I ran into a new issue:

Query1:
SyntaxEditor Code Snippet

select to_timestamp(RegExp_Replace(Regexp_Replace(column_name,':','.',1,3),'\b([0-9])\b', '0\1'),'mm/dd/yyyy hh:mi:ss.ff6') as column_name from Table_A

 Error: Teradata hour of day must be in range 1-12

Query2:

SyntaxEditor Code Snippet

select to_timestamp(RegExp_Replace(Regexp_Replace(column_name,':','.',1,3),'\b([0-9])\b', '0\1'),'mm/dd/yyyy hh24:mi:ss.ff6') as column_name from Table_A

 Error: Unmatched element in date string.

 

Any other way to specify the 24 hour format?

Junior Contributor

Re: Trying to convert a varchar to timestamps(6) in select query

To_Timestamp(RegExp_Replace(Column_Name,'\b([0-9])\b', '0\1'),'mm/dd/yyyy hh24:mi:ss:ff6')
Enthusiast

Re: Trying to convert a varchar to timestamps(6) in select query

I use this query and it gives an error "Unmatched value in date string"

 

SyntaxEditor Code Snippet

select to_timestamp(RegExp_Replace(Regexp_Replace(column_name,':','.',1,3),'\b([0-9])\b', '0\1'),'mm/dd/yyyy hh24:mi:ss:ff6') as datetime 
from Table_A

I have found an alternate way to do it but I still would like the simpler query.

 

Alternate Query:

select top 100 cast(
substr(RegExp_Replace(Regexp_Replace(column_name,':','.',1,3),'\b([0-9])\b', '0\1'),7,4)|| '/' ||
substr(RegExp_Replace(Regexp_Replace(column_name,':','.',1,3),'\b([0-9])\b', '0\1'),1,2)|| '/' ||
substr(RegExp_Replace(Regexp_Replace(column_name,':','.',1,3),'\b([0-9])\b', '0\1'),4,2)|| ' ' ||
substr(RegExp_Replace(Regexp_Replace(column_name,':','.',1,3),'\b([0-9])\b', '0\1'),12,12)
as Timestamp(6)) as column_name
from Table_A
 

Tags (1)
Junior Contributor

Re: Trying to convert a varchar to timestamps(6) in select query

There's no need to replace the last colon with a period, TO_TIMESTAMP works with colon, too.

But if you replace it you must modify the format accordingly.

 

To_Timestamp(RegExp_Replace(Column_Name,'\b([0-9])\b', '0\1'),'mm/dd/yyyy hh24:mi:ss:ff6')

To_Timestamp(RegExp_Replace(RegExp_Replace(Column_Name,':','.',1,3),'\b([0-9])\b', '0\1'),'mm/dd/yyyy hh24:mi:ss.ff6')
Enthusiast

Re: Trying to convert a varchar to timestamps(6) in select query

This is the query I'm trying to execute now, but the error is still "Unmatched element in date string". Not able to figure out what the error is about

 

SyntaxEditor Code Snippet

select to_timestamp(RegExp_Replace(Regexp_Replace(column_name,':','.',1,3),'\b([0-9])\b', '0\1'),'mm/dd/yyyy hh24:mi:ss.ff6') as column_name
from Table_A