Daylight Savings Time - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Daylight Savings Time

Author

Topic

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-11-05 : 09:22:32

Hi,
This has nothing to do with SQL right.
In General....

Like if the MSSQL service was run under a certain account does it inherit the profile Data/Time settings of that profile?
Windows setting Automatically adjust for date time(check/uncheck). So If I run a stored proc that used GETDATE() owner was dbo. ???? Is there any cause/effect.

I've got an abbreation. It has to do with files that were touched then saved elsewhere but the touch time is manually entered via a web frontend that then calls a proc to write a date. So I don't know where to start looking. Was one of the fileservers set to the wrong date, was the IIS server, was the Proc doing it?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-05 : 12:11:32

Accounts do not have anything to do with date and time settings. It doesn't matter who runs GETDATE(), GETDATE() will always return the current date and time of the database server. The database server adjusts for daylight savings time if the flag is set in the registry.

So no the proc wasn't doing it if it was using GETDATE().

Tara

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-11-05 : 12:22:40

Thank you,
Now I know where to begin. It realy is an interesting problem I have here and wasn't one I was expecting. Now I don't know if I should convert some datetime values in the database to UTC or not.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-05 : 12:24:32

That's what we do. All times are stored in GMT. We then use a sql function to convert them to the appropriate time for the client, which is dependent upon a user setting. I wrote this function. If you need it, just let me know. It calls two other functions plus one table. It works very well.

Tara

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-11-05 : 13:22:14

This might be different in that large files (CAD) are issued to a certain network location. The current process has them going through a clearing house of sorts where they are manually timestamped using the touch command. Once the files have a modified date that date is manually entered in the user interface. This manual action is the absolutly most goofed up thing and I fought it so hard going into building this thing but everybody was "no that isn't important just recreate the form we have been using". Thing is the modified date is NTFS implimentation of grand time since day "1" similar to datetime data type, and the displayed modified date as you browse the network is offset (daylight savings time corrected) machine dependent. Because I'm not interacting with the filesystem modified times (touch)are always going to be different than manually entered times. The whole thing makes no sense to me and never has. I saw your functions but this is SQL7.

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-05 : 14:25:05

quote:
Originally posted by tduggan

I wrote this function. If you need it, just let me know. It calls two other functions plus one table. It works very well.

[toolbox]
help me
[/toolbox]

Ok, I don't need it (yet)...but there's alway a possibility...

Does SQL team do this?

Or is everything EST?

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-05 : 14:29:53

Here are the UDFs:



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[udf_FirstSundayOfTheMonth]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[udf_FirstSundayOfTheMonth]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[udf_LastSundayOfTheMonth]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[udf_LastSundayOfTheMonth]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[udf_Timezone_Conversion]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[udf_Timezone_Conversion]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




----------------------------------------------------------------------------------------------------
-- OBJECT NAME		: udf_FirstSundayOfTheMonth
--
-- AUTHOR		: Tara Duggan
-- DATE			: June 5, 2002
--
-- INPUTS		: @Date
-- OUTPUTS		: @Day
--
-- DEPENDENCIES		: None
--
-- APPLICATION(s)	: This function is called by another function, udf_Timezone_Conversion.
--
-- DESCRIPTION		: This function returns the date of the first Sunday of the month 
--			  when given any date during that month.
--                          
-- EXAMPLES (optional)	: SELECT dbo.udf_FirstSundayOfTheMonth(getdate())
--
-- MODIFICATION HISTORY :
----------------------------------------------------------------------------------------------------
-- MM/DD/YYYY - (Name)
-- (Description)
----------------------------------------------------------------------------------------------------
CREATE  FUNCTION udf_FirstSundayOfTheMonth
  ( @Date datetime )
RETURNS datetime
AS
BEGIN
  DECLARE @weekday int
  DECLARE @day datetime
  DECLARE @number int
  SELECT @number = 1
  SELECT @weekday = 0
  WHILE @weekday <> 1
  BEGIN
    SELECT @day = (CAST(STR(MONTH(@Date)) + '/' +  STR(@number) + '/' + STR(YEAR(@Date)) AS DateTime))
    SELECT @weekday = DATEPART(weekday, @day)
    SELECT @number = @number + 1
  END
  RETURN @day
END






GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




----------------------------------------------------------------------------------------------------
-- OBJECT NAME		: udf_LastSundayOfTheMonth
--
-- AUTHOR		: Tara Duggan
-- DATE			: June 5, 2002
--
-- INPUTS		: @Date
-- OUTPUTS		: @Day
--
-- DEPENDENCIES		: None
--
-- APPLICATION(s)	: This function is called by another function, udf_Timezone_Conversion.
--
-- DESCRIPTION		: This function returns the date of the last Sunday of the month 
--			  when given any date during that month.
--                          
-- EXAMPLES (optional)	: SELECT dbo.udf_LastSundayOfTheMonth(getdate())
--
-- MODIFICATION HISTORY :
----------------------------------------------------------------------------------------------------
-- MM/DD/YYYY - (Name)
-- (Description)
----------------------------------------------------------------------------------------------------
CREATE  FUNCTION udf_LastSundayOfTheMonth
  ( @Date datetime )
RETURNS datetime
AS
BEGIN
  DECLARE @weekday int
  DECLARE @Lastday datetime
  DECLARE @number int
  DECLARE @day datetime
  SELECT @weekday = 0
  SELECT @Lastday = (DATEADD(day, -1, CAST(STR(MONTH(@Date)+1) + '/' + STR(01) + '/' + STR(YEAR(@Date)) AS DateTime)))
  SELECT @number = DATEPART(day, @Lastday)
  WHILE @weekday <> 1
  BEGIN
    SELECT @day = (CAST(STR(MONTH(@Date)) + '/' +  STR(@number) + '/' + STR(YEAR(@Date)) AS DateTime))
    SELECT @weekday = DATEPART(weekday, @day)
    SELECT @number = @number - 1
  END
  RETURN @day
END





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO







----------------------------------------------------------------------------------------------------
-- OBJECT NAME		: udf_Timezone_Conversion
--
-- AUTHOR		: Tara Duggan
-- DATE			: June 5, 2002
--
-- INPUTS		: @Source_Timezone, @Destination_Timezone, 
--			  @Source_datetime, @Display_Timezone
-- OUTPUTS		: @converted_datetime
--
-- DEPENDENCIES		: None
--
-- APPLICATION(s)	: All applications can utilize this function.
--
-- DESCRIPTION		: This function converts a given date and time 
--			  from one time zone to another.
--                          
-- EXAMPLES (optional)	: SELECT dbo.udf_Timezone_Conversion('PST', 'EST', getdate(), 0)
--
-- MODIFICATION HISTORY :
----------------------------------------------------------------------------------------------------
-- MM/DD/YYYY - (Name)
-- (Description)
----------------------------------------------------------------------------------------------------
CREATE   FUNCTION udf_Timezone_Conversion
(@Source_Timezone varchar(25), @Destination_Timezone varchar(25), 
@Source_datetime datetime, @Display_Timezone bit = 0)
RETURNS varchar(50)
AS

BEGIN
-------------------------------------------------------------------------------------------------------------
--  Declarations
-------------------------------------------------------------------------------------------------------------
DECLARE @Source_DST bit
DECLARE @Destination_DST bit
DECLARE @converted_date datetime
DECLARE @converted_timezone varchar(50)
DECLARE @year int
DECLARE @AprilDate datetime
DECLARE @OctDate datetime
DECLARE @DST_Start datetime
DECLARE @DST_End datetime
DECLARE @GMT_Offset_Source int
DECLARE @GMT_Offset_Destination int
DECLARE @converted_datetime varchar(50)
-------------------------------------------------------------------------------------------------------------
--  Initializations
-------------------------------------------------------------------------------------------------------------
SELECT @year = DATEPART(year, @Source_datetime)
SELECT @AprilDate = 'Apr 15 ' + CONVERT(char(4), @year) 
SELECT @OctDate = 'Oct 15 ' + CONVERT(char(4), @year) 
SELECT @DST_Start = DATEADD(hour, 2, (dbo.udf_FirstSundayOfTheMonth(@AprilDate)))
SELECT @DST_End = DATEADD(hour, 2, (dbo.udf_LastSundayOfTheMonth(@OctDate)))
SELECT @DST_End = DATEADD(second, -1, @DST_End)

SELECT @GMT_Offset_Source = GMT_Offset
FROM TIMEZONE
WHERE Timezone_Name = @Source_Timezone

SELECT @GMT_Offset_Destination = GMT_Offset
FROM TIMEZONE
WHERE Timezone_Name = @Destination_Timezone

SELECT @Source_DST = DST_bit
FROM TIMEZONE
WHERE Timezone_Name = @Source_Timezone

SELECT @Destination_DST = DST_bit
FROM TIMEZONE
WHERE Timezone_Name = @Destination_Timezone
-------------------------------------------------------------------------------------------------------------
--  Check for valid inputs
-------------------------------------------------------------------------------------------------------------
IF @Source_Timezone NOT IN (SELECT Timezone_Name FROM TIMEZONE) 
  OR @Destination_Timezone NOT IN (SELECT Timezone_Name FROM TIMEZONE)
	RETURN 'You have entered an invalid time zone.'
-------------------------------------------------------------------------------------------------------------
--  Source date and time are during DST and both time zones observe DST
--  Convert source time zone to GMT, then convert GMT to destination time zone
--  Check if destination date and time are not in DST after the conversion
-------------------------------------------------------------------------------------------------------------
IF (@Source_datetime BETWEEN @DST_Start AND @DST_End) AND (@Destination_DST = 1) AND (@Source_DST = 1)
BEGIN
	SELECT @converted_date = DATEADD(MINUTE, - @GMT_Offset_Source - 60, @Source_datetime)
			
	SELECT @converted_date = DATEADD(MINUTE, @GMT_Offset_Destination + 60, @converted_date)
		
	IF @converted_date NOT BETWEEN @DST_Start AND @DST_End
	BEGIN
		SELECT @converted_timezone = @Destination_Timezone
		SELECT @converted_date = DATEADD(MINUTE, -60, @converted_date)
	END
	ELSE
		SELECT @converted_timezone = DST_Abbrv
		FROM TIMEZONE
		WHERE Timezone_Name = @Destination_Timezone
END	
-------------------------------------------------------------------------------------------------------------
--  Source data and time are not during DST
--  Convert source time zone to GMT, then convert GMT to destination time zone
--  Check if destination data and time are in DST after the conversion
--  If destination date and time are in DST, check if it observes DST
-------------------------------------------------------------------------------------------------------------
ELSE IF (@Source_datetime NOT BETWEEN @DST_Start AND @DST_End) 
  OR ((@Source_datetime BETWEEN @DST_Start AND @DST_End) AND (@Destination_DST = 0) AND (@Source_DST = 0))
BEGIN 
	SELECT @converted_date = DATEADD(MINUTE, - @GMT_Offset_Source, @Source_datetime)
	
	SELECT @converted_date = DATEADD(MINUTE, @GMT_Offset_Destination, @converted_date)

	IF (@converted_date BETWEEN @DST_Start AND @DST_End) AND (@Destination_DST = 1)
	BEGIN
		SELECT @converted_date = DATEADD(MINUTE, 60, @converted_date)

		SELECT @converted_timezone = DST_Abbrv
		FROM TIMEZONE
		WHERE Timezone_Name = @Destination_Timezone
	END
	ELSE
		SELECT @converted_timezone = @Destination_Timezone
END	
-------------------------------------------------------------------------------------------------------------
--  Source date and time are during DST and only source time zone observes DST
--  Convert source time zone to GMT, then convert GMT to destination time zone
--  Check if destination date and time are not in DST after the conversion
-------------------------------------------------------------------------------------------------------------
ELSE IF (@Source_datetime BETWEEN @DST_Start AND @DST_End) AND (@Destination_DST = 0) AND (@Source_DST = 1)
BEGIN
	SELECT @converted_date = DATEADD(MINUTE, - @GMT_Offset_Source - 60, @Source_datetime)
	
	SELECT @converted_date = DATEADD(MINUTE, @GMT_Offset_Destination, @converted_date)
		
	SELECT @converted_timezone = @Destination_Timezone
END
-------------------------------------------------------------------------------------------------------------
--  Source date and time are during DST and only destination time zone observes DST
--  Convert source time zone to GMT, then convert GMT to destination time zone
--  Check if destination date and time are not in DST after the conversion
-------------------------------------------------------------------------------------------------------------
ELSE IF (@Source_datetime BETWEEN @DST_Start AND @DST_End) AND (@Destination_DST = 1) AND (@Source_DST = 0)
BEGIN
	SELECT @converted_date = DATEADD(MINUTE, - @GMT_Offset_Source, @Source_datetime)
		
	SELECT @converted_date = DATEADD(MINUTE, @GMT_Offset_Destination + 60, @converted_date)
		
	IF @converted_date NOT BETWEEN @DST_Start AND @DST_End
	BEGIN
		SELECT @converted_timezone = @Destination_Timezone
		SELECT @converted_date = DATEADD(MINUTE, -60, @converted_date)
	END
	ELSE
		SELECT @converted_timezone = DST_Abbrv
		FROM TIMEZONE
		WHERE Timezone_Name = @Destination_Timezone
END
-------------------------------------------------------------------------------------------------------------
--  Format the output using style 21
-------------------------------------------------------------------------------------------------------------	
IF @Display_Timezone = 0
	SELECT @converted_datetime = CONVERT(varchar(50), @converted_date, 21)
ELSE 
	SELECT @converted_datetime = CONVERT(varchar(50), @converted_date, 21) + '; ' + UPPER(@converted_timezone)
-------------------------------------------------------------------------------------------------------------
--  Return the output
-------------------------------------------------------------------------------------------------------------
RETURN @converted_datetime

END











GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Here is the table:



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TIMEZONE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TIMEZONE]
GO

CREATE TABLE [dbo].[TIMEZONE] (
	[Timezone_Name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[GMT_Offset] [float] NOT NULL ,
	[DST_Abbrv] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[DST_bit] [bit] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TIMEZONE] ADD 
	CONSTRAINT [PK_TIMEZONE] PRIMARY KEY  CLUSTERED 
	(
		[Timezone_Name]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

Here is the data for the timezone table (comma separated, data was bcp'ed out):


+0200 ,120.0,      ,0

+0300 ,180.0,      ,0

+0400 ,240.0,      ,0

+0500 ,300.0,      ,0

+0530 ,330.0,      ,0

+0600 ,360.0,      ,0

+0700 ,420.0,      ,0

+0800 ,480.0,      ,0

+0930 ,570.0,      ,0

+1000 ,600.0,      ,0

+1100 ,660.0,      ,0

+1200 ,720.0,      ,0

-0300 ,-180.0,      ,0

-0400 ,-240.0,      ,0

-0500 ,-300.0,      ,0

-1100 ,-660.0,      ,0

-1200 ,-720.0,      ,0

AHST,-600.0,HDT,1

ALASKA,-540.0,ALASKA,1

ARIZ  ,-420.0,      ,0

AST   ,-240.0,ADT   ,1

CST   ,-360.0,CDT   ,1

EST   ,-300.0,EDT   ,1

GMT,0.0, ,0

HKT   ,480.0,      ,0

HST,-600.0,      ,0

INDANA,-300.0,      ,0

IRAN  ,210.0,      ,0

JST   ,540.0,      ,0

KST   ,270.0,      ,0

MEXCTY,-360.0,MEXCDT,1

MST   ,-420.0,MDT   ,1

NFLD  ,-210.0,NF    ,1

PST   ,-480.0,PDT   ,1

QUEBECE,-240.0, ,0

SASKAT,-360.0,      ,0

The above timezones are the only ones that the function supports. The GMT offset is in seconds. This was done to support future timezones that aren't currently supported.

Tara

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-05 : 14:32:52

Very cool Tara!!

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-11-05 : 16:58:58

I'm still unclear on what to do really. I present a browse list to folks which has the database stored touch time of a file, this time was manually entered. But see browsing the network inherits the system time of whatever computer it is on. So enterprise wide if I get all the settings the same on all clients and servers the value that was stored in the database is still going to need the filter of -1 applied to it when it is a daylight savings time day. See not all the dates are self refereencing. The database saved date is only a representaion of the actual file date. Like two separate data sources. One gets a filter-> the network file modified date as it appears in windows explorer, the other one a date at some time when that file was touched remains unfiltered. I guess it is like two clients in the T'dugg's example except they have different offsets. Like the database recorded touch time of the file is correct. ie. it WAS 12:00 when I moved a file to another server. And until we went back to EST that's what the file said. Now it's an hour younger? That ain't right. This is uneven application of an offset WRT the file system display.

SELECT
A.formid, A.sono, A.xref, A.jobno, A.giveto, A.filenames, A.needsinchconv, A.doneinchconv, A.updatedjobdoc, A.updatedsurflog,
A.visualMX, A.visualMY, A.visualROT, A.visualOTHER, A.visualCUTASINMODEL,
A.rot1, A.rot2, A.rot3, A.rot4, A.rot5, A.filename1, A.filesize1, A.descriptionx,
A.notes, A.invalid,B.statusid, B.formid, B.userstep1, B.step1done, B.step1date, B.userstep2, B.step2done, B.step2date,
B.userstep3, B.step3done,
CASE
 WHEN
 CONVERT(varchar(10),GETDATE(),101) NOT IN (SELECT convert(varchar(10),dayxxx,101) from EDT_days)THEN  --------------- currently in non-daylight savings time
  CASE WHEN convert(varchar(10),B.step3date,101) IN (SELECT convert(varchar(10),dayxxx,101) from EDT_days) THEN                        

  DATEADD(hh,-1,B.step3date)                                                                                                                                                          Files touched during DST need a –1 hour 

    ELSE B.step3date END                                                                                                                                                                Files touched during non-DST can be left as is
 ELSE                        ------------------------------------------------------------------------------- currently in daylight savings time
  CASE WHEN convert(varchar(10),B.step3date,101) NOT IN (SELECT convert(varchar(10),dayxxx,101) from EDT_days) THEN                 
  DATEADD(hh,1,B.step3date)                                                                                                                                                          Files touched during non-DST need 1 hour added to touch time

ELSE B.step3date END                                                                                                                                                                   Files touched during DST can be left as is
 END step3date

So here is a nested case solution that makes use of
EDT_days table
---
2003-04-06 00:00:00.000
2003-04-07 00:00:00.000
2003-04-08 00:00:00.000
2003-04-09 00:00:00.000

this ain't quick, I'm gonna try EDT_days table as
char to get rid of one set of converts on dayxxx.

Just as a capper I ended up with an EDT_days table for the next 15 years, stored the dates like 2003/10/24 to get rid of the second convert in he above query. The whole queries in application went from ~3 seconds to sub 1 second. Good enough. One day we will upgrade to 2000 and I can play with the big kids.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources