Function for Time Conversions

Question

Function for Time Conversions

Jake-346339

Grasshopper

Points: 21
More actions
July 6, 2006 at 1:25 pm

#168831

Hello all, here with a question:
I have a database that is logging data in UTC time. I need to access this data with a query and display it in the Local Time for the system accessing it. The system accessing the database will not change timezones, but I need to take daylight savings into account since its a historical server.
I have been told that a possible round would be to implement a function on SQL Server that would take in the time zone and give the appropriate conversion. However, my knowledge of creating user functions in SQL is limited.
Currently, I have SQL scripts that run the query on the database and I believe a function would allow me to keep the scripts the same, yet do the conversion for me in the background so the correct data is displayed. Any help is greatly appreciated.
Jake

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135510 More actions · Answer 1

keep in mind that if you access an indexed column to which you apply a function, will make the predicate non-sargeble. Meaning the index will not be used and the function will be applyed in a second stage.

IMO you would be better off converting your local time to UTC-time and then pass it to a query ! so you will not need any conversion to solve the query.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jake-346339 Grasshopper Points: 21 More actions · Answer 2

Could you explain this a little more thoroughly? I want to make sure I am understanding it correctly.

Johan Bijnens SSC Guru Points: 135510 More actions · Answer 3

What I mean is that if you want to e.g. select data with a where-clause which has your UTC time used as a predicate, it's best to convert your search-criteria to UTC time befor you pass it to SQLServer.

If you're not using the UTC-column in where clauses, you only need a function to convert your UTC to local datetime.

Keep in mind this function needs to be aware what's the timezone the query is launched from and when date and time were switched for daylight-savings. Because this conversion can be done (by the dbengine) after the select, no this will not influence the selection, unless you perform an order-by or group-by on the local-datetime result column.