UTC to Local Time conversion in Microsoft SQL Server 2008 question.
Please help me to fill the dots below.
create function dtUtc2Local(@dtUtc datetime)returns datetime as
--Parameter: UTC datetime
--returns: Corresponding local time
begin
Declare @dtLocal as datetime
...
SELECT @dtLocal = ...
return @dtLocal
END
go
Is you proposed solution daylight-saving-aware please?
MS Windows OS.
Thanks.
create function dtUtc2Local(@dtUtc datetime)returns datetime as
--Parameter: UTC datetime
--returns: Corresponding local time
begin
Declare @dtLocal as datetime
...
SELECT @dtLocal = ...
return @dtLocal
END
go
Is you proposed solution daylight-saving-aware please?
MS Windows OS.
Thanks.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sorry, thats no correct
Try
Try
create function dtUtc2Local(@dtUtc datetime)returns datetime as
--Parameter: UTC datetime
--returns: Corresponding local time
begin
DECLARE @TimeDiff int
Declare @dtLocal as datetime
--get time difference
SET @TimeDiff = DATEDIFF(hh, GETUTCDATE(), GETDATE())
SELECT @dtLocal = DATEADD(HH, @TimeDiff, @dtUtc)
return @dtLocal
ENDASKER
The code returns the same "-5" time offset for both within and outsode daytime saving period.
It should be "-5" for winter (EST) and "-4" for summer (EDT) here in Maryland, USA.
-----------------------
2010-12-31 19:00:00.000
(1 row(s) affected)
-----------------------
2011-06-30 19:00:00.000
(1 row(s) affected)
It should be "-5" for winter (EST) and "-4" for summer (EDT) here in Maryland, USA.
-----------------------
2010-12-31 19:00:00.000
(1 row(s) affected)
-----------------------
2011-06-30 19:00:00.000
(1 row(s) affected)
begin tran
go
create function dtUtc2Local(@dtUtc datetime)returns datetime as
--Parameter: UTC datetime
--returns: Corresponding local time
begin
DECLARE @TimeDiff int
Declare @dtLocal as datetime
--get time difference
SET @TimeDiff = DATEDIFF(hh, GETUTCDATE(), GETDATE())
SELECT @dtLocal = DATEADD(HH, @TimeDiff, @dtUtc)
return @dtLocal
END
go
select dbo.dtUtc2Local('1/1/2011 12:00 am')
select dbo.dtUtc2Local('7/1/2011 12:00 am')
rollback
You are going to have to write a .NET app to get the offset. Also, keep in mind that there are timezones that are 30 minutes apart and not a full hour.
ASKER
Do you mean the desribed function cannot be implemented in T-SQL at all, acperkins please?
It can be, it is just that as you have discovered you will need to adjust for DST. I have never done this before, but perhaps you could do this if you had a table of dates dating back to the first entry that shows whether it is DST date or not. This may seem at first overkill, but consider that doing a lookup on a table is far simpler and something that SQL Server does rather well, compared to trying to calculate whether that particular date for that year was DST or not. If you go back say 5 years that is less than 2000 rows of data.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I wrote a function to convert UTC time to our local Estern Time zone.
What I want is to convert it to whatever time zone that is stored in the client's OS, say in Alaska (pronouns instead of literals wanted).
What I want is to convert it to whatever time zone that is stored in the client's OS, say in Alaska (pronouns instead of literals wanted).
begin tran
go
create function dtUtc2EasternTime(
@dtUtc datetime
)returns datetime as
--Parameter: @dtUtc - UTC datetime
--returns: Corresponding local time
begin
Declare @dtLocal as datetime =
dateadd(hh,
case when
@dtUtc between convert(datetimeoffset,'03/11/2007 2:00 am -05:00') and convert(datetimeoffset,'11/04/2007 2:00 am -05:00') or
@dtUtc between convert(datetimeoffset,'03/09/2008 2:00 am -05:00') and convert(datetimeoffset,'11/02/2008 2:00 am -05:00') or
@dtUtc between convert(datetimeoffset,'03/08/2009 2:00 am -05:00') and convert(datetimeoffset,'11/01/2009 2:00 am -05:00') or
@dtUtc between convert(datetimeoffset,'03/14/2010 2:00 am -05:00') and convert(datetimeoffset,'11/07/2010 2:00 am -05:00') or
@dtUtc between convert(datetimeoffset,'03/13/2011 2:00 am -05:00') and convert(datetimeoffset,'11/06/2011 2:00 am -05:00') or
@dtUtc between convert(datetimeoffset,'03/11/2012 2:00 am -05:00') and convert(datetimeoffset,'11/04/2012 2:00 am -05:00') or
@dtUtc between convert(datetimeoffset,'03/10/2013 2:00 am -05:00') and convert(datetimeoffset,'11/03/2013 2:00 am -05:00') or
@dtUtc between convert(datetimeoffset,'03/09/2014 2:00 am -05:00') and convert(datetimeoffset,'11/02/2014 2:00 am -05:00') or
@dtUtc between convert(datetimeoffset,'03/08/2015 2:00 am -05:00') and convert(datetimeoffset,'11/01/2015 2:00 am -05:00')
then -4
else case when datepart(yyyy,@dtUtc) between 2007 and 2015
then -5
else datediff(hh,GetUtcDate(),GetDate())
end
end, @dtUtc)
return @dtLocal
END
go
select dbo.dtUtc2EasternTime('1/1/2010 12:00 am')
select dbo.dtUtc2EasternTime('7/1/2010 12:00 am')
select dbo.dtUtc2EasternTime('1/1/2011 12:00 am')
select dbo.dtUtc2EasternTime('7/1/2011 12:00 am')
rollback
END
go
select dbo.dtUtc2EasternTime('1/1/2010 12:00 am')
select dbo.dtUtc2EasternTime('7/1/2010 12:00 am')
select dbo.dtUtc2EasternTime('1/1/2011 12:00 am')
select dbo.dtUtc2EasternTime('7/1/2011 12:00 am')
rollbackASKER
I found solution myself
ASKER
Argument data type datetime is invalid for argument 2 of dateadd function.