Converting a string to proper timestamp

Analytics

Converting a string to proper timestamp

Hello

 

I'm despertly trying to convert a string to a timestamp. The issue that our system records them in the following fashion :

13.7.2018 9:38:11

 

which does not work, i believe it should be like 13/07/2018 09:38:11 to work (if the european format is supported)

 

Any idea how to manipulate the string to obtain the conversion ?

 

Best regards

 

Vincent


Accepted Solutions
Teradata Employee

Re: Converting a string to proper timestamp

Hi Vincent,

 

Here your only problem is the month being coded on 1 digit.

If you're on TD 15.10+ you can use this kind of query :

with cte_data (str) as (select '13.7.2018 9:38:11' (varchar(50)))
select str
     , to_timestamp(RegExp_Replace(str, '[0-9]+', right(('0' || regexp_substr(str, '[0-9]+', 1, 2) (varchar(3))), 2), 1, 2), 'dd.mm.yyyy hh24:mi:ss') as str_ts
  from cte_data;

str                 str_ts
-----------------   --------------------------
13.7.2018 9:38:11   2018-07-13 09:38:11.000000

If've put all the functions together but feel free to evaluate them one by one.

1 ACCEPTED SOLUTION
3 REPLIES
Teradata Employee

Re: Converting a string to proper timestamp

Hi Vincent,

 

Here your only problem is the month being coded on 1 digit.

If you're on TD 15.10+ you can use this kind of query :

with cte_data (str) as (select '13.7.2018 9:38:11' (varchar(50)))
select str
     , to_timestamp(RegExp_Replace(str, '[0-9]+', right(('0' || regexp_substr(str, '[0-9]+', 1, 2) (varchar(3))), 2), 1, 2), 'dd.mm.yyyy hh24:mi:ss') as str_ts
  from cte_data;

str                 str_ts
-----------------   --------------------------
13.7.2018 9:38:11   2018-07-13 09:38:11.000000

If've put all the functions together but feel free to evaluate them one by one.

Re: Converting a string to proper timestamp

Hi

 

A million thanks, it worked beautifully !

 

Best regards

 

Vincent

Junior Contributor

Re: Converting a string to proper timestamp

The RegEx can be simplified to prepend a space before any single digit:

RegExp_Replace(str, '\b([0-9])\b', '0\1')

And if you don't need/want the 6 fractional digits returned by To_Timestamp you can use a Teradata cast:

Cast(RegExp_Replace(str, '\b([0-9])\b', '0\1') AS TIMESTAMP(0) FORMAT 'dd.mm.yyyyBhh:mi:ss')