Adjusting a Timestamp - 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)

Adjusting a Timestamp

Author

Topic

dvadams
Starting Member

4 Posts

Posted - 2002-12-13 : 15:52:26

Hello,

I currently have a timestamp in a table that records in Greenwich Mean Time.
When running my query I need the time converted to CST (I need to subtract
6 hours). Any thoughts on how I can accomplish this? Should I just subtract
6 from the Eventtime (the column that stores this data). How would I script
this? Any suggestions would be appreciated! I'll post my query below.

Thanks,
Dorothy Adams
Conserve School Technology Specialist

SELECT Count(HX_EVENTS.EVENTID)
FROM NT3400.dbo.HX_AUXDATA HX_AUXDATA, NT3400.dbo.HX_ENTRIES HX_ENTRIES,
NT3400.dbo.HX_EVENTS HX_EVENTS
WHERE HX_AUXDATA.ENTRYID = HX_ENTRIES.ENTRYID AND HX_AUXDATA.ENTRYID = HX_EVENTS.ENTRYID
AND HX_AUXDATA.OBJSITE = HX_ENTRIES.OBJSITE AND HX_AUXDATA.OBJSITE = HX_EVENTS.OBJSITE
AND HX_ENTRIES.ENTRYID = HX_EVENTS.ENTRYID AND HX_ENTRIES.OBJSITE = HX_EVENTS.OBJSITE
AND ((HX_ENTRIES.EVENTTIME>=? And HX_ENTRIES.EVENTTIME<=?) AND (HX_AUXDATA.ITEMDATA='LAB
Cafe Cashier #1') AND (Datepart(Hour,HX_ENTRIES.EVENTTIME) Between 20 And
23))

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2002-12-13 : 16:04:55

You could just subtract 6, but what happens when it is daylight savings time. You will need to subtract off 5. Are you going to change your code when this happens? I create a user-defined function to do this. You just have to check the current date to see if you are in daylight savings time or not and adjust accordingly.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-13 : 16:34:13

You can use

WHERE DATEPART(hh,DATEADD(hh, -6, HX_ENTRIES.EVENTTIME)) BETWEEN 20 AND 23

Tduggan can you share your udf.

Edited by - ValterBorges on 12/13/2002 16:36:01

dvadams
Starting Member

4 Posts

Posted - 2002-12-13 : 16:45:57

Thanks for the suggestion. Unfortunately when I tried to run the adjusted script using the modified Datepart function you suggested, I received the following error/prompt: "Parameters are not allowed in queries that cannot be displayed graphically". I wonder what that error is referring to? The modified datepart function makes sense, so I don't know why it's not working (I'm running this query in MS Query).

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-13 : 17:05:55

I believe your error occurs due to the following
((HX_ENTRIES.EVENTTIME>=? And HX_ENTRIES.EVENTTIME<=?)

You don't need two filters
DATEPART(hh,DATEADD(hh, -6, HX_ENTRIES.EVENTTIME)) BETWEEN 20 AND 23

Can you post your table definition with some data.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2002-12-13 : 17:07:37

The UDF requires a TIMEZONE table and it only supports the time zones that are listed in the TIMEZONE table. I will be adding the functionality for the rest of the time zones once this project that I am on needs it (should be in a few months).

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] WITH NOCHECK ADD
CONSTRAINT [PK_TIMEZONE] PRIMARY KEY CLUSTERED
(
[Timezone_Name]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

Data (fields separated by a comma):
+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

Here is the UDF:

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

Edited by - tduggan on 12/13/2002 17:08:52

Edited by - tduggan on 12/13/2002 17:10:45

Edited by - tduggan on 12/13/2002 17:13:10

Edited by - tduggan on 12/13/2002 17:16:15

dvadams
Starting Member

4 Posts

Posted - 2002-12-13 : 19:14:34

Figured it out. I actually needed to include the DATEADD function in the SELECT portion of my statement rather than in the WHERE portion. That did exactly what I needed it to do....thanks for your suggestions!

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-13 : 19:40:41

Nice Script tduggan!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources