cast char string to timestamp

Database
Enthusiast

cast char string to timestamp

Hi

I have a problem with a char string that I need to convert to a timestamp.

I have tried the following:

1): SELECT CAST('20110405153449' AS TIMESTAMP(6) FORMAT 'YYYYMMDDhhmiss')

2): SELECT CAST('20110405153449261873' AS TIMESTAMP(6) FORMAT 'YYYYMMDDhhmiss.ssssss')

3): SELECT CAST('2011/04/05 15:34:49.261873' AS TIMESTAMP(6) FORMAT 'YYYYMMDDhhmiss.ssssss')

4): SELECT CAST('2011/04/05 15:34:49.261873' AS TIMESTAMP(6))

number 1 is working, but my char string has 6 dicits of millisecounds.

number 2 and number 3 are not working

number 4 is working, but I would prefere to minimize the need of string manipulation.

Why are nu 2 not working?? Any help??

br

Peter Schwennesen

4 REPLIES
Fan

Re: cast char string to timestamp

Hi to anyone that happens to find this question and are looking for answers.


 On 1) The output has 6 digits of microseconds because code specify timestamp(6)
   The format literal given will not change (i.e. truncate) the datatype result.

   To see output as the given format literal suggests, code ...as timestamp(0)...


 On 2) If the input string had included a decimal separator, it may work:

  SELECT CAST('20110405153449.261873' AS TIMESTAMP(6) FORMAT 'YYYYMMDDhhmiss.s(6)')


 On 3) The format literal is not matching the actual input string format.

   Add separators, B for blank, and s(n) for the microseconds, like:

  SELECT CAST('2011/04/05 15:34:49.261873' AS TIMESTAMP(6) FORMAT 'YYYY/MM/DDBhh:mi:ss.s(F)')  --use F for the number of characters needed to display the fractional seconds precision.
  SELECT CAST('2011/04/05 15:34:49.261873' AS TIMESTAMP(6) FORMAT 'YYYY/MM/DDBhh:mi:ss.s(6)')  --static 6 digits

 

By the way, I use TD version 15.10,
format literals explained: https://docs.teradata.com/reader/WurHmDcDf31smikPbo9Mcw/z~gI2cuqLdMwttrVP1Pjng

 

 

Highlighted
Junior Contributor

Re: cast char string to timestamp

To_Timestamp (unlike FORMAT) allows omitting the decimal seperator:

 To_Timestamp('20110405153449261873' , 'YYYYMMDDHH24MISSFF6')
Fan

Re: cast char string to timestamp

How can the result be as timestamp(0) starting with a timestamp(3), and without cast to char?
Use case: the timestamp(0)-value to be used in a join (I could cast from zero precision to 3 probably), but anyway:

select current_timestamp(3) as in_Tms ,cast(cast(in_Tms as char(19)) as timestamp(0)) as result_Tms
--How to achieve this without cast to char?
Junior Contributor

Re: cast char string to timestamp

Your cast truncates the subsecond, this can be achieved more efficiently by subtracting the fractional second from the timestamp:

in_Tms - (INTERVAL '1' SECOND * (Extract(SECOND From in_Tms) MOD 1))

The result is still a timestamp(3)