Conversion to local time

Transact-SQL
1

I've got all database times in UTC datetime2 format. I need to convert customer times to local times in reports and account for daylight savings.

I'm happy to add a time zone table.

What's the best method in SQL 2008?

Sam

2

You don't need to add a separate table for this.SQL can do this in your select statement

Have a look at this
http://stackoverflow.com/questions/8038744/convert-datetime-column-from-utc-to-local-time-in-select-statement

3

Hi Ahmed,

This solution appears to convert to my local time, or the local time of the server.

I have a server running on GMT/UTC and all times for various clients are collected in UTC. This setup results in GETUTCDATE and GETDATE returning the same value

Clients in the database are not all in the same location, so some kind of time zone ID for each client is needed.

When pulling a report for a client, times in the database need to be converted to the client's time zone. This isn't hard except for the daylight savings shift that occurs twice a year.

This seems to indicate that the date/time being converted to local time needs to be examined to see if it falls into daylight savings - or not - then an appropriate conversion applied.

Is there a neat solution for this problem?

Sam