Convert SQL datetime from UTC to local
I have a MS SQL database in which all datetimes are stored as UTC(GMT). All work that this database keeps track of is in the EST time zone. I need to make a stored procedure that can convert a given datetime to whatever the local time should be. I've figured out how to do this with an offset and the DATEADD() function, but I dont know how to determine the appropriate offset for the datetime. I could use this:
DATEADD(hour,DATEDIFF(hour
but this calculates the offset based on the current time, which fails in the case where I am calculating a datetime from daylight savings at a time that isnt in daylight savings.
It seems like if I could seed GETDATE and GETUTCDATE with the date I am working with that would work, but I dont know how or if I can do that.
Thanks!
DATEADD(hour,DATEDIFF(hour
but this calculates the offset based on the current time, which fails in the case where I am calculating a datetime from daylight savings at a time that isnt in daylight savings.
It seems like if I could seed GETDATE and GETUTCDATE with the date I am working with that would work, but I dont know how or if I can do that.
Thanks!
Seven price
there is a defined function in the CRM sql database called "fn_UTCToLocalTime"
ASKER
What do you mean CRM sql database?
Sorry try this. You are not using the crm
UTC datetime values in SQL Server 2000
You can convert local datetime values to UTC datetime values, and vice-versa, using the built-in GETUTCDATE() function:
DECLARE @LocalDate DATETIME
SET @LocalDate = GETDATE()
-- convert local date to utc date
DECLARE @UTCDate DATETIME
SET @UTCDate = DATEADD(Hour, DATEDIFF(Hour, GETUTCDATE(), GETDATE()), @LocalDate)
-- convert utc date to local date
DECLARE @LocalDate2 DATETIME
SET @LocalDate2 = DATEADD(Hour, DATEDIFF(Hour, GETDATE(), GETUTCDATE()), @UTCDate)
SELECT @LocalDate, @UTCDate, @LocalDate2
Note that GETUTCDATE() returns the current datetime in UTC. By comparing the value with GETDATE() we can determine the time zone, which can then be used to adjust any date.
I tried to bake these expressions into a set of user-defined functions, but SQL Server complained because user-defined functions cannot call non-deterministic functions (in this case GETDATE()/GETUTCDATE()).
UTC datetime values in SQL Server 2000
You can convert local datetime values to UTC datetime values, and vice-versa, using the built-in GETUTCDATE() function:
DECLARE @LocalDate DATETIME
SET @LocalDate = GETDATE()
-- convert local date to utc date
DECLARE @UTCDate DATETIME
SET @UTCDate = DATEADD(Hour, DATEDIFF(Hour, GETUTCDATE(), GETDATE()), @LocalDate)
-- convert utc date to local date
DECLARE @LocalDate2 DATETIME
SET @LocalDate2 = DATEADD(Hour, DATEDIFF(Hour, GETDATE(), GETUTCDATE()), @UTCDate)
SELECT @LocalDate, @UTCDate, @LocalDate2
Note that GETUTCDATE() returns the current datetime in UTC. By comparing the value with GETDATE() we can determine the time zone, which can then be used to adjust any date.
I tried to bake these expressions into a set of user-defined functions, but SQL Server complained because user-defined functions cannot call non-deterministic functions (in this case GETDATE()/GETUTCDATE()).
ASKER
these fns, getdate and getutcdate return the CURRENT datetimes don't they? As I said in the question, this fails for the situation where I am GMT-5 some parts of the year and GMT-4 other times.
There are several web discussions about this issue, the most versatile solution offered is to create a timezone / DST table which you can lookup the appropriate offset hours from UTC. I saw some mention of obtaining the server's DST status via API but no example code.
Using the current date and the results of datediff(hour, getdate(), getutcdate()) as a key you could return from the table the timezone and whether daylight savings was in effect for that date.
Using the current date and the results of datediff(hour, getdate(), getutcdate()) as a key you could return from the table the timezone and whether daylight savings was in effect for that date.
ASKER
so there is no way to ask the operating system via SQL Server? I know there are libraries in php for example where you can pass a date and it can return to you whether or not the given date is in DST and I think it queries the OS because it works even when DST changes, as long as the OS is up to date patch wise. folderol, what did you google to find these threads that discuss alternatives?
google search
"function to return the time zone from Windows"
Discusses the change in USA for start of DST
this is the API calls. you have to use some method like this as SQL Server does not have an internal function or statement that returns the current server setting.
http://msdn.microsoft.com/en-us/library/ms724421(VS.85).aspx
Discusses using registry from within EXCEL,
http://www.pcreview.co.uk/forums/thread-2788903.php
"function to return the time zone from Windows"
Discusses the change in USA for start of DST
this is the API calls. you have to use some method like this as SQL Server does not have an internal function or statement that returns the current server setting.
http://msdn.microsoft.com/en-us/library/ms724421(VS.85).aspx
Discusses using registry from within EXCEL,
http://www.pcreview.co.uk/forums/thread-2788903.php
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Uau, I needed exactly the same thing and the accepted solution is MARVELLOUS!
Thanks a LOT, guys!
Regards,
fskilnik.
Thanks a LOT, guys!
Regards,
fskilnik.