Convert London time to be Standard Universal Time

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Convert London time to be Standard Universal Time

Author

Topic

micnie_2020
Posting Yak Master

232 Posts

Posted - 2012-07-02 : 03:30:50

Hi,

I have table column DateTime stored as London Time. How can i used sql to convert it based on regional display time based on Client Side (IE)?

Please advise?

Thank you.

Regards,
Micheale

micnie_2020
Posting Yak Master

232 Posts

Posted - 2012-07-02 : 05:13:04

I had change my regional setting to London Timezone.

Then i used below script:-

-- Convert a UTC Time to a Local Time
DECLARE @UTCDate datetime
DECLARE @LocalDate datetime
DECLARE @TimeDiff int

-- Figure out the time difference between UTC and Local time
SET @UTCDate = GETUTCDATE()
SET @LocalDate = GETDATE()
SET @TimeDiff = DATEDIFF(hh, @UTCDate, @LocalDate)

--Check Results
PRINT @LocalDate
PRINT @UTCDate
PRINT @TimeDiff

The Times for @UTCDate is missing 1 hour. I found is missing daylight savings. How can i add the daylight savings unto it by using sql to detect the variance of daylight saving time?

Jul 2 2012 5:10PM
Jul 2 2012 9:10AM --> Supposed to be Jul 2 2012 10:10AM; displayed at the right bottom corner timestamp shown Jul 2 2012 10:10AM.
8

Please advise.

Thank you.

Regards,
Micheale

micnie_2020
Posting Yak Master

232 Posts

Posted - 2012-07-02 : 21:32:55

Hi,

I manage to get it done using below method. But can anyone check for me whether my method is correctly?

CREATE function [dbo].[fn_GetDaylightSavingsTimeStart]
(@Year varchar(4))
RETURNS smalldatetime
as
begin
declare @DTSStartWeek smalldatetime, @DTSEndWeek smalldatetime
set @DTSStartWeek = '03/01/' + convert(varchar,@Year)
return case datepart(dw,@DTSStartWeek)
when 1 then
dateadd(hour,170,@DTSStartWeek)
when 2 then
dateadd(hour,314,@DTSStartWeek)
when 3 then
dateadd(hour,290,@DTSStartWeek)
when 4 then
dateadd(hour,266,@DTSStartWeek)
when 5 then
dateadd(hour,242,@DTSStartWeek)
when 6 then
dateadd(hour,218,@DTSStartWeek)
when 7 then
dateadd(hour,194,@DTSStartWeek)
end
end

-----------------------

CREATE function [dbo].[fn_GetDaylightSavingsTimeEnd]
(@Year varchar(4))
RETURNS smalldatetime
as
begin
declare @DTSEndWeek smalldatetime
set @DTSEndWeek = '11/01/' + convert(varchar,@Year)
return case datepart(dw,dateadd(week,1,@DTSEndWeek))
when 1 then
dateadd(hour,2,@DTSEndWeek)
when 2 then
dateadd(hour,146,@DTSEndWeek)
when 3 then
dateadd(hour,122,@DTSEndWeek)
when 4 then
dateadd(hour,98,@DTSEndWeek)
when 5 then
dateadd(hour,74,@DTSEndWeek)
when 6 then
dateadd(hour,50,@DTSEndWeek)
when 7 then
dateadd(hour,26,@DTSEndWeek)
end
end

------------------------

Create procedure [dbo].[getDayLightSavingTime]
@Date datetimeoffset
--exec [getDayLightSavingTime] '2011-01-03 01:30:27.183'
AS
SET NOCOUNT ON;
BEGIN

declare @DLSStart smalldatetime
, @DLSEnd smalldatetime
, @DLSActive tinyint
set @DLSStart = (select PHTMaster.dbo.fn_GetDaylightSavingsTimeStart(convert(varchar,datepart(year,getdate()))))
set @DLSEnd = (select PHTMaster.dbo.fn_GetDaylightSavingsTimeEnd(convert(varchar,datepart(year,getdate()))))

if @Date between @DLSStart and @DLSEnd
begin
set @DLSActive = 1
end
else
begin
set @DLSActive = 0
end

if(@DLSActive=1)
begin
SELECT SWITCHOFFSET(@Date, '+01:00') AS LocalTime
end
else
select LocalTime=@Date

END
SET NOCOUNT OFF;

Thank you.

Regards,
Micheale

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources