Parsing dates from diferent timezones
-
I have a SQL Server running in GMT. I have been passed a VARCHAR which holds a date and time in BST . I want to be able to convert the VARCHAR into a DateTime and also convert the date into GMT. I have no problems paseing the VARCHAR into a DateTime but I can't see a way of converting a time in BST into a time in GMT.
-
can you post an example of the data - sureley you just need to use dateadd(hh,1,[date]) and dateadd(hh,-1,[date]) to go backwards and forwards one hour ???
MVDBA
-
dateadd( h, x, cast( datefield as datetime))
where x is the hour difference. datefield is your column.
-
Frank Kalis pointed me toward a way to find out the local timezone using the registry :
DECLARE @delta INT
EXEC master.dbo.xp_regread
'HKEY_LOCAL_MACHINE'
, 'SYSTEM\CurrentControlSet\Control\TimeZoneInformation'
, 'ActiveTimeBias'
, @delta OUT
SELECT
GETDATE() AS Local_Time
, DATEADD( Minute, @delta,GETDATE()) AS Greenwich_Mean_Time
, @delta / 60 AS Delta
Also check out http://www.sqlservercentral.com/columnists/dasanka/datetimevaluesandtimezones.asp
by Dinesh Asanka.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
The problem is BST is sometimes the same as GMT and sometimes it is fowards and hour. The logic for when the change happens is not trivial and I was wondering if there was a simple way around it rather than having to do it by hand. A defination of bst is:
British Summer Time (BST) is the daylight saving time in effect in the United Kingdom between the last Sunday in March and the last Sunday in October each year. BST is the same as Central European Time: one hour in advance of Greenwich Mean Time, UTC+1.
-
Maybe you can read the reg-key from within your application's front-tier (not at the mid-tier or server) and have the timezoneshift delivered by your application.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
so what you're asking really is -- is there a way to detect whether it's BST or not from the current date? and then apply a date modifier if it is ??????
MVDBA
-
Would rather not have to do that as I would mean rewriting rather a lot of code.
-
I know that the date is in BST my question is: is there an easy way of converting a date in BST into GMT. It is not as simple as a date add since BST is sometimes an hour ahead of GMT sometimes it is the same time.
-
alzdba: Getting the registry key out like you suggest would work very well if the server was in BST but unfortinatly it is in GMT so I can get the ActiveTimeBias for BST. Still that is a neat trick I will rember that one.
-
Maybe this udf can be used as a workaround :
if exists (select * from dbo.sysobjects where id = object_id('dbo.udf_Get_Last_DayNr'))
begin
Drop Function dbo.udf_Get_Last_DayNr
print 'Dropped'
end
go
Create Function dbo.udf_Get_Last_DayNr
( @Start_DateTime AS datetime = '1900-01-01'
, @End_DateTime AS datetime
, @SearchDayNr as tinyint = 1
, @hh_time as char(2) = '00')
returns datetime
As
BEGIN
-- ALZDBA dd 24/07/2003
-- Determine last date from daynumber between two dates
-- Remark : Sunday = dayno 1 ... unless SET DATEFIRST { number | @number_var } is in use
if @Start_DateTime > @End_DateTime
begin
-- print '-- wrong INPUTPARAMETER(1)'
return(null)
end
if @SearchDayNr between 1 and 7
begin
set @SearchDayNr = @SearchDayNr
end
else
begin
-- print '-- wrong INPUTPARAMETER(3)'
return(null)
end
Declare @Dw_End_DateTime as smallint
Select @Dw_End_DateTime = DATEPART(dw,@End_DateTime)
Declare @Verschil as smallint
Select @Verschil = @Dw_End_DateTime - @SearchDayNr
if @Verschil = 0
begin
return convert(datetime,convert(varchar(11),@End_DateTime,121) + @hh_time + ':00:00')
end
else
begin
Select @Verschil = case when @Verschil < 1 then (7 + @Verschil ) * (-1)
else @Verschil * (-1)
end
Declare @Result_DateTime DateTime
Select @Result_DateTime = dateadd(dd, @Verschil ,@End_DateTime)
if @Result_DateTime < @Start_DateTime
begin
-- print 'Result before startdate'
return (NULL)
end
else
begin
if @Result_DateTime > @End_DateTime
begin
-- print 'Result after enddate'
return (null)
end
else
begin
return convert(datetime, convert(varchar(11),@Result_DateTime,121) + @hh_time + ':00:00')
end
end
end
Return(NULL)
END
go
GRANT EXECUTE ON dbo.udf_Get_Last_DayNr TO public
GO
-- execute it
select dbo.udf_Get_Last_DayNr ( '2004-03-01' , '2004-03-31', 1 , '02') as SummertimeOnDate
, dbo.udf_Get_Last_DayNr ( '2004-10-01' , '2004-10-31', 1 , '02') as SummertimeOffDate
You'll get the grip .
Or you can just check if there is a timedifference between your input and your sqlserver, if there is , it is summertime
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
alzdba: Hmm yes doing the conversion by hand does seem to be the only way. Thanks for the code.
Lionel
-
How about using a UDF? Here's an example:
CREATE FUNCTION dbo.fnGMT
(
@bstDateStr varchar(22)
)
RETURNS datetime
AS
BEGIN
-- British Summer Time (BST) is the daylight saving time in effect in the United Kingdom
-- between the last Sunday in March and the last Sunday in October each year.
-- BST is the same as Central European Time: one hour in advance of Greenwich Mean Time, UTC+1.
--
-- Normal = GMT (or WET, Western European Time)
-- Daylight Saving = BST = GMT + 1
-- All changes occur at 1am GMT
DECLARE @bstYearStr varchar(4), @bstDate datetime
DECLARE @bstStart datetime, @bstEnd datetime
SET @bstDate = Convert(datetime, @bstDateStr)
-- Compute Beginning and Ending DateTimes of BST
SET @bstYearStr = Convert(varchar(4), DatePart(yyyy, @bstDate))
SET @bstStart = Convert(datetime, '03/01/' + @bstYearStr + ' 01:00:00')
SET @bstEnd = Convert(datetime, '10/31/' + @bstYearStr + ' 01:00:00')
WHILE DatePart(dw, @bstStart) <> 1
SELECT @bstStart = DateAdd(d, 1, @bstStart)
WHILE DatePart(dw, @bstEnd) <> 1
SELECT @bstStart = DateAdd(d, -1, @bstStart)
IF @bstDate BETWEEN @bstStart AND @bstEnd
SET @bstDate = DateAdd(hh, -1, @bstDate)
RETURN @bstDate
END
/*
-- TEST
PRINT dbo.fnGMT('1/1/2004 00:00:00')
PRINT dbo.fnGMT('2/26/2004 00:00:00')
PRINT dbo.fnGMT('3/1/2004 00:00:00')
PRINT dbo.fnGMT('3/7/2004 00:00:00')
PRINT dbo.fnGMT('3/7/2004 01:00:00')
PRINT dbo.fnGMT('3/7/2004 02:00:00')
PRINT dbo.fnGMT('7/26/2004 00:00:00')
PRINT dbo.fnGMT('10/30/2004 00:00:00')
PRINT dbo.fnGMT('10/31/2004 00:00:00')
PRINT dbo.fnGMT('10/31/2004 01:00:00')
PRINT dbo.fnGMT('10/31/2004 00:59:59')
PRINT dbo.fnGMT('10/31/2004 02:00:00')
PRINT dbo.fnGMT('11/15/2004 00:00:00')
PRINT dbo.fnGMT('12/31/2004 00:00:00')
*/
-
We solved the same issue using an extended stored procedure. The complicated logic of reading the registry and adjusting for the bias and the complicated daylight savings time logic is all handled by the windows call SystemTimeToTzSpecificLocalTime. We just wrapped some code around it and it handles it. If you are not worried about handling daylight savings time issues then getdate() - getutcdate() will give you the bias from UTC time.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply