Convert UTC to local 1

[MikeM2468]

I have a database that stores dates as UTC. I need to perform a query but convert the output to local time. Is there an easy way to do that? It's also important that the query find items relative to local time.

The date column would have entries hourly and I would want to spit out a result showing the entries for the last 24 hours. This is SQL server 2005.

 

[gmmastros]

What do you mean by "local" time? Is this local to the SQL Server computer, or do you mean local to the user?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[MikeM2468]

local to the server

 

[gmmastros]

SQL server has a GetUTCDate() function. You can use this to determine the difference in hours between your server's time and UTC time.

Ex:

Select DateDiff(Hour, GetUTCDate(),GetDate())

When I run this on my server, I get -4. That means I need to subtract 4 hours from local time to get UTC time. Alternatively, I could add 4 hours to UTC time to get my local time.

This value can change throughout the year based on daylight savings time. Not all countries observe daylight savings, and even those that do may use different dates for when daylight savings occur. So, you could be 4 hours different most of the time, but 5 hours different occasionally.

Bottom line, though, you can use this offset to determine what your where clause criteria should be.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[MikeM2468]

Thanks. I was looking at that. I guess I just need to figure out the logic to search back 24 hours from the current time but convert the output to local time.

 

[gmmastros]

It should be relatively straight forward.

To get the data that you want...

Select Columns,
       DateAdd(Hour, [!]-4[/!], YourUTCColumn) As LocalDateTime
From   YourTable
Where  YourUTCColumn >= DateAdd(Day, -1, GetUTCDate)

Note that I hardcoded the value -4 in to the query, I did this just to make the code a little clearer to understand. To integrate the hour offset, you could do this.

Select DateAdd(Hour, DateDiff(Hour, GetUTCDate(), GetDate()), YourUTCColumn) As LocalTime
From   YourTableName
Where  YourUTCColumn >= DateAdd(Day, -1, GetUTCDate())

Of course, this assumes that you won't have any future dates in the table. If you do, then you'll need to add an additional where clause criteria, like this.

Where  YourUTCColumn >= DateAdd(Day, -1, GetUTCDate())
       And YourUTCColumn < GetUTCDate()

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[MikeM2468]

Thanks, this helped me get what I needed.