wrong timestamp displayed

Database
Enthusiast

wrong timestamp displayed

select timestamp format

Hi Team,

what is wrong in the following SQL.

 

SELECT TO_TIMESTAMP ('03/26/2015 04:00:32', 'MM/dd/YYYY hh:MI:SS');



it returns

"3/26/2015 10:00:32.000000"

instead of 04:00:32 it returns 10:00:32. how to fix this issue?

Thanks

Krishna




3 REPLIES
Enthusiast

Re: wrong timestamp displayed

use the below query to convert the time to your own timezone

SELECT TO_TIMESTAMP ('03/26/2015 04:00:32', 'MM/dd/YYYY hh:MI:SS' ) AT TIME ZONE 'gmt+6' ;
Enthusiast

Re: wrong timestamp displayed

This time I am getting 

3/26/2015 22:00:32.000000

I am looking for the same data that i have inserted.

Teradata Employee

Re: wrong timestamp displayed

TO_TIMESTAMP follows Oracle rules. 

CAST('03/26/2015 04:00:32' TO TIMESTAMP(0) FORMAT'mm/dd/yyyyBhh:mi:ss')

Or if it's a constant, just timestamp'2015-03-26 04:00:34'

As far as time zone displacements are concerned: Teradata expects you to identify or default the source time zone when you load the data, so it can be "normalized" for storage & comparisons. Similarly you specify or default the desired time zone when you query the data. Defaults can be set at session, user, or system level. If you use TIMESTAMP WITH TIME ZONE, then "AT SOURCE" is an additional option for queries.