asked on

Extract time from timestamp in teradata while dropping seconds and milliseconds

I am trying to convert a timestamp formatted like the following example, 'yyyy-mm-dd hh:mm:ss.fff', to just the time in hours and minutes. I will be using this field later as a grouping dimension so it's important that the seconds and milliseconds are dropped. I would prefer that the result value still be interpreted as true time it is not required so if I were to get a string I would be okay. Lastly and most important the timestamp is in UTC time and I need it to be converted to American eastern.

Any assistance with this would be very much appreciated. Thank you

Bradley Fox

This expression in SQL studio will give you HH:MM in -5 GMT (Eastern US) timezone assuming you pass in UTC. You can replace GETUTCDATE() with your variable.

SELECT CONVERT(VARCHAR(5),DATEADD(hh,-5,GETUTCDATE()),8)

Open in new window

Addie Baker

ASKER

Ah Ha. I was using convert(varchar, timestamp, 108) trying to convert it. In regards to the UTC conversion though. I was under the assumption that UTC time takes into consideration day light savings time so although on this current date we are -5, a few months from now we will be -4. With that said, wouldnt there have to be a way for the function to interpret the date that is being passed?

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Addie Baker

ASKER

Just tried it out and worked like a charm. Thank you very much for your assistance.

PortletPaul

A pleasure, thanks for the grading. Cheers, Paul

oh, & out of interest were you able to use it without converting to char or varchar?