Break Date Into Date Range

Question

Break Date Into Date Range

BSavoie

SSCertifiable

Points: 6309
More actions
January 14, 2011 at 10:17 pm

#232307

I have a table that lists "Holidays":
DECLARE @Holiday TABLE (HolidayId int identity(1,1) primary key, HolidayDate DateTime)
INSERT INTO @Holiday(HolidayDate) VALUES('7/4/2011')
INSERT INTO @Holiday(HolidayDate) VALUES('12/24/2011')
INSERT INTO @Holiday(HolidayDate) VALUES('12/25/2011')
INSERT INTO @Holiday(HolidayDate) VALUES('12/31/2011')
INSERT INTO @Holiday(HolidayDate) VALUES('8/11/2011') --* Yea, my birthday is a holiday in my db :)
I need a query to transform holidays into a range like this:
HolidayId Starts Ends
===========================================================
1 7/4/2011 12:00:00 AM 7/4/2011 11:59:59 PM
2 12/24/2011 12:00:00 AM 12/24/2011 11:59:59 PM
.
.
.
Any easy way to do this?
.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

BSavoie SSCertifiable Points: 6309 More actions · Answer 1

OK, maybe this is silly, but here's what I came up with. If I understand correctly, this would encapsulate the entire range of the holiday, right down to the millisecond (almost)

SELECT DATEADD(Ms, 2, CAST(HolidayDate AS DateTime)) AS HolidayStartTime, DATEADD(Ms, -2, CAST(DATEADD(day, 1, HolidayDate) AS DateTime)) AS HolidayEndTime FROM Holiday

Any thoughts?

.

WayneS SSC Guru Points: 95460 More actions · Answer 2

How's this?

DECLARE @Holiday TABLE (HolidayId int identity(1,1) primary key, HolidayDate DateTime)

INSERT INTO @Holiday(HolidayDate) VALUES('7/4/2011')

INSERT INTO @Holiday(HolidayDate) VALUES('12/24/2011')

INSERT INTO @Holiday(HolidayDate) VALUES('12/25/2011')

INSERT INTO @Holiday(HolidayDate) VALUES('12/31/2011')

INSERT INTO @Holiday(HolidayDate) VALUES('8/11/2011') --* Yea, my birthday is a holiday in my db :)

SELECT HolidayID,

Starts = HolidayDate,

Ends = DateAdd(ms, -2, DateAdd(day, 1, HolidayDate))

FROM @Holiday;

My thoughts:

It looks like you are on SQL 2008. What happens if this datatype gets changes to one of the new datetime2 datatypes? You know, with precision to the 100 nano-seconds?

This is why most people will do the dates as:

declare @StartDate datetime, @EndDate datetime;

set @StartDate = DateAdd(day, DateDiff(day, 0, @StartDate),0);

set @EndDate = DateAdd(day, 1, @StartDate);

SELECT ...

FROM ...

WHERE DateField >= @StartDate -- starts sometime today

AND DateField < @EndDate; -- less than tomorrow

This will work if the time precision goes out to 1000 decimal places.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

BSavoie SSCertifiable Points: 6309 More actions · Answer 3

That makes good sense. I guess I'm paranoid about times, and I tend to over analyze. Seems like times always come back to bite me.

Thanks!

.