sql server programming: QUERY HELP NEEDED: datetime field losing format when I use select -- sql server programming

Re: QUERY HELP NEEDED: datetime field losing format when I use select

Dan Guzman
11/18/2006 3:43:15 PM

[quoted text, click to view]

Your case statement should return both date and time as illustrated by the
example below. However, it seems to me that you daylight savings time
algorithm needs some tweaking ( I assume you are converting UTC to Central
time). Beginning in 2007 in the US, daylight savings begins the second
Sunday in march and ends the first Sunday in November. Personally, I'd use
a table with UTC time zone offsets for the conversion.

CREATE TABLE #Test_DateTime
(
DATETIME1 datetime NOT NULL
)
INSERT INTO #Test_DateTime
SELECT '20070331 23:00:00'
UNION ALL SELECT '20070401 00:00:00'
UNION ALL SELECT '20070401 01:00:00'

SELECT
CASE WHEN DATETIME1 < '04/01/2007 12:00:00 AM'
THEN DATEADD(HH, - 6, DATETIME1)
ELSE DATEADD(HH, - 5, DATETIME1) END
FROM #Test_DateTime

-----------------------
2007-03-31 17:00:00.000
2007-03-31 19:00:00.000
2007-03-31 20:00:00.000

--
Hope this helps.

Dan Guzman
SQL Server MVP

[quoted text, click to view]

Re: QUERY HELP NEEDED: datetime field losing format when I use sel

jlewis
11/18/2006 7:34:01 PM

Hi Dan,

I can't thank you enough for your help. Would you show me exactly where to
plug in your table into my entire code? I REALLY appreciate your ideas :)
Janet

Here's what I have so far:

SELECT nvarchar6 AS Sport, nvarchar5 AS [Day], CASE WHEN DATETIME1 <
'4/01/2007 5:00:00 AM' THEN DATEADD(HH, - 6, DATETIME1) ELSE DATEADD(HH, - 5,
DATETIME1) END AS [Date], nvarchar1 AS Opponent, nvarchar2 AS Site, datetime1
FROM UserData
WHERE (tp_ListId = '{E0A96D80-5D28-4348-B2F0-695157846888}') AND
(nvarchar6 = N'golf, boys') AND (datetime1 >= GETDATE() - 365)
ORDER BY datetime1

[quoted text, click to view]

Re: QUERY HELP NEEDED: datetime field losing format when I use sel

jlewis
11/19/2006 7:50:01 AM

Hi Dan,

This is excellent. I will use your idea for this project and save it for my
other ones. Thank you so much! Janet

[quoted text, click to view]

Re: QUERY HELP NEEDED: datetime field losing format when I use sel

Dan Guzman
11/19/2006 9:19:55 AM

[quoted text, click to view]

The example below uses a time zone conversion table to facilitate converting
a UTC datetime to local time for the specified time zone. It's also
possible to accomplish the task without actually materializing the start/end
times bit I've found this method provides the best performance and is
easiest to implement.

CREATE TABLE dbo.TimeConversionUtcOffsets
(
TimeZone varchar(100) NOT NULL,
StartTimeUTC datetime NOT NULL,
EndTimeUTC datetime NOT NULL,
Offset int NOT NULL,
CONSTRAINT PK_TimeConversionUtcOffsets PRIMARY KEY
(
TimeZone,
StartTimeUTC
)
)
GO

INSERT INTO TimeConversionUtcOffsets(TimeZone, StartTimeUTC, EndTimeUTC,
Offset)
UNION ALL SELECT 'Central Time (US & Canada)', '20060403 08:00:00',
'20061030 07:00:00', -5
UNION ALL SELECT 'Central Time (US & Canada)', '20061030 07:00:00',
'20070312 08:00:00', -6
UNION ALL SELECT 'Central Time (US & Canada)', '20070312 08:00:00',
'20071105 07:00:00', -5
UNION ALL SELECT 'Central Time (US & Canada)', '20071105 07:00:00',
'20080309 08:00:00', -6
GO

SELECT
nvarchar6 AS Sport,
nvarchar5 AS [Day],
DATEADD(hh, Offset, datetime1) AS [Date],
nvarchar1 AS Opponent,
nvarchar2 AS Site,
datetime1
FROM dbo.UserData
JOIN dbo.TimeConversionUtcOffsets ON
TimeZone = 'Central Time (US & Canada)' AND
datetime1 >= StartTimeUTC AND
datetime1 < EndTimeUTC
WHERE
tp_ListId = '{E0A96D80-5D28-4348-B2F0-695157846888}' AND
nvarchar6 = N'golf, boys' AND
datetime1 >= DATEADD(year, -1, GETUTCDATE())
ORDER BY datetime1
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.