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??
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
To_Timestamp (unlike FORMAT) allows omitting the decimal seperator:
To_Timestamp('20110405153449261873' , 'YYYYMMDDHH24MISSFF6')
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?
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)