All Forums Database
kmanivan82 10 posts Joined 02/12
23 Apr 2015
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

 

Anilnandala 16 posts Joined 03/14
24 Apr 2015

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' ;

 

kmanivan82 10 posts Joined 02/12
24 Apr 2015

This time I am getting 

3/26/2015 22:00:32.000000

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

Fred 1096 posts Joined 08/04
28 Apr 2015

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.

You must sign in to leave a comment.