SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How many more Mondays until I retire?


How many more Mondays until I retire?

Author
Message
John Rempel
John Rempel
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1029 Visits: 289
Well done Peter. I like the versatility. Great examples.

You could even populate the holiday table this way, at least when they are defined like the Civic Holiday or Labour Day (in Canada) as the first Monday in August and September, respectively. For holidays dependant on the lunar cycle, like Easter... whatcha gonna do?
SwePeso
SwePeso
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5171 Visits: 3433

The best way would be to use this function

CREATE FUNCTION dbo.GetEasterSunday
(
@Y INT
)
RETURNS SMALLDATETIME
AS
BEGIN
DECLARE @EpactCalc INT,
@PaschalDaysCalc INT,
@NumOfDaysToSunday INT,
@EasterMonth INT,
@EasterDay INT

SET @EpactCalc = (24 + 19 * (@Y % 19)) % 30
SET @PaschalDaysCalc = @EpactCalc - (@EpactCalc / 28)
SET @NumOfDaysToSunday = @PaschalDaysCalc - ((@Y + @Y / 4 + @PaschalDaysCalc - 13) % 7)
SET @EasterMonth = 3 + (@NumOfDaysToSunday + 40) / 44
SET @EasterDay = @NumOfDaysToSunday + 28 - (31 * (@EasterMonth / 4))

RETURN
(
SELECT CONVERT
(
SMALLDATETIME,

RTRIM(@Y)
+ RIGHT('0'+RTRIM(@EasterMonth), 2)
+ RIGHT('0'+RTRIM(@EasterDay), 2)
)
)
END

Taken from http://www.aspfaq.com/show.asp?id=2519




N 56°04'39.16"
E 12°55'05.25"
SwePeso
SwePeso
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5171 Visits: 3433

Yes, I did benchmark your code Razvan. Did you try your test-code yourself?
Running your test-code above gives 763 milliseconds for my function and 1,563 milliseconds for your function.

Mine is faster and I think that is because SQL is not very fast handling two variables as in your function.




N 56°04'39.16"
E 12°55'05.25"
Richard Thomas-189729
Richard Thomas-189729
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 18

I had the idea of populating the Holiday table myself this morning. I found a function to calculate the date for Easter on this page:

http://www.databasejournal.com/scripts/article.php/3469911

Then I used that along with Peter's seqDates function to create the following SP. Covers all UK Holidays, and if New Year, Christmas or Boxing Day occur at the weekend it inserts the appropriate Monday/Tuesday into the table.

CREATE PROCEDURE GenerateHolidays (@yr SMALLINT)

AS

DECLARE @tmpdatec VARCHAR(10)
DECLARE @tmpdate SMALLDATETIME
DECLARE @tmpdesc VARCHAR(50)
DECLARE @sdate VARCHAR(10)
DECLARE @edate VARCHAR(10)

SET @tmpdatec = '01/01/' + CAST(@yr AS VARCHAR)
SET @tmpdate = CAST(@tmpdatec AS SMALLDATETIME)
IF DATEPART(dw, @tmpdate) = 7 SELECT @tmpdate = DATEADD(DAY, 1, @tmpdate)
IF DATEPART(dw, @tmpdate) = 1 SELECT @tmpdate = DATEADD(DAY, 1, @tmpdate)
SET @tmpdesc = 'New Year''s Day'
INSERT INTO Holidays VALUES (@tmpdate, @tmpdesc)

SELECT @tmpdate = dbo.fnYear2Easter(@yr)
SELECT @tmpdate = DATEADD(DAY, -2, @tmpdate)
SELECT @tmpdesc = 'Good Friday'
INSERT INTO Holidays VALUES (@tmpdate, @tmpdesc)

SELECT @tmpdate = DATEADD(DAY, 3, @tmpdate)
SELECT @tmpdesc = 'Easter Monday'
INSERT INTO Holidays VALUES (@tmpdate, @tmpdesc)

SET @sdate = '05/01/' + CAST(@yr AS VARCHAR)
SET @edate = '05/31/' + CAST(@yr AS VARCHAR)

SELECT @tmpdate = MIN(dt.SeqDate) FROM dbo.fnSeqDates(@sdate, @edate) dt WHERE DATEPART(dw, dt.SeqDate) = 2
SET @tmpdesc = 'May Day Bank Holiday'
INSERT INTO Holidays VALUES (@tmpdate, @tmpdesc)

SELECT @tmpdate = MAX(dt.SeqDate) FROM dbo.fnSeqDates(@sdate, @edate) dt WHERE DATEPART(dw, dt.SeqDate) = 2
SET @tmpdesc = 'Spring Bank Holiday'
INSERT INTO Holidays VALUES (@tmpdate, @tmpdesc)

SET @sdate = '08/01/' + CAST(@yr AS VARCHAR)
SET @edate = '08/31/' + CAST(@yr AS VARCHAR)

SELECT @tmpdate = MAX(dt.SeqDate) FROM dbo.fnSeqDates(@sdate, @edate) dt WHERE DATEPART(dw, dt.SeqDate) = 2
SET @tmpdesc = 'August Bank Holiday'
INSERT INTO Holidays VALUES (@tmpdate, @tmpdesc)

SET @tmpdatec = '12/25/' + CAST(@yr AS VARCHAR)
SET @tmpdate = CAST(@tmpdatec AS SMALLDATETIME)
IF DATEPART(dw, @tmpdate) = 7 SELECT @tmpdate = DATEADD(DAY, 1, @tmpdate)
IF DATEPART(dw, @tmpdate) = 1 SELECT @tmpdate = DATEADD(DAY, 1, @tmpdate)
SET @tmpdesc = 'Christmas Day'
INSERT INTO Holidays VALUES (@tmpdate, @tmpdesc)

SET @tmpdatec = '12/26/' + CAST(@yr AS VARCHAR)
SET @tmpdate = CAST(@tmpdatec AS SMALLDATETIME)
IF DATEPART(dw, @tmpdate) = 7 SELECT @tmpdate = DATEADD(DAY, 2, @tmpdate)
IF DATEPART(dw, @tmpdate) = 1 SELECT @tmpdate = DATEADD(DAY, 2, @tmpdate)
SET @tmpdesc = 'Boxing Day'
INSERT INTO Holidays VALUES (@tmpdate, @tmpdesc)



R2ro
R2ro
SSC Veteran
SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)SSC Veteran (223 reputation)

Group: General Forum Members
Points: 223 Visits: 134

I too wrote a slightly different version... Same binary concept, implemented slightly differently with a twist! It allows the specification of an increment or step value.

CREATE FUNCTION dbo.udf_genDateSequence
(
@prmLoDate datetime, -- lower date boundary (starting value)
@prmHiDate datetime, -- upper date boundary (maximum value)
@prmIncDays int -- increment value
)
RETURNS @Dates TABLE (DateVal datetime NOT NULL PRIMARY KEY)
AS
/*

Function: Generate a table of dates.

Strategy: Set-based scalar value generation.

Usage: dbo.udf_genDateSequence(fromValue, toValue, stepValue)

*/

BEGIN
DECLARE
@daysDiff int,
@swapVar datetime

IF @prmLoDate > @prmHiDate
BEGIN
SET @swapVar = @prmLoDate
SET @prmLoDate = DATEADD(day, DATEDIFF(day, 0, @prmHiDate), 0)
SET @prmHiDate = DATEADD(day, DATEDIFF(day, 0, @swapVar), 0)
END
ELSE
BEGIN
SET @prmLoDate = DATEADD(day, DATEDIFF(day, 0, @prmLoDate), 0)
SET @prmHiDate = DATEADD(day, DATEDIFF(day, 0, @prmHiDate), 0)
END

SET @daysDiff = DATEDIFF(day, @prmLoDate, @prmHiDate)

INSERT INTO @Dates VALUES(@prmLoDate)

WHILE @prmIncDays <= @daysDiff
BEGIN
INSERT INTO @Dates
SELECT DATEADD(day, @prmIncDays, DateVal)
FROM @Dates
WHERE DateVal <= DATEADD(day, -@prmIncDays, @prmHiDate)

SET @prmIncDays = @prmIncDays * 2
END

RETURN
END
go


Fal
Fal
SSChasing Mays
SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)

Group: General Forum Members
Points: 602 Visits: 1803

Why am I using the DATEADD and DATEDIFF functions here?
They are used to remove time information! I want to be sure that only date information is used. So I set the time to 00:00:00.000 the fastest way I know.

[ie: DATEADD(day, DATEDIFF(day, 0, getdate()), 0) ]

I've not seen this method used before. Although it pops up from time-to-time on this site, the "best" method I knew for stripping the time was:
cast(floor(cast(getdate() as float)) as datetime)
After some testing (with a rather clunky while loop) I got the following table:
iterationsdatedifffloor
100
1000
10000
100076
100006875
100000712810
Iterations is the number of times the method was used, run 100 times each, and then an average taken. Seems to be that if you're performing a low number of "time strips" there is negligible difference. Indeed, for 100,000 iterations there is only a 98 millisecond gain. Less than 1 second.
So, *is* there a real difference of one method over the other?
S.

Razvan Socol
Razvan Socol
SSC Eights!
SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)

Group: General Forum Members
Points: 967 Visits: 129

Nice work, R2ro! It seems that your function is the best, so far. Here are some timings (in ms) for the three versions (counting the rows in the result, for the dates between Jan 1, 2006 and Jan 31, 2307):

PeterRazvanR2ro
19061670

766

20762846796
8301653876
906610766
8601703873
31702846703

As you can see, on my system, the timings are varying strangely, but on average, your method seems to be the best.

Razvan





SwePeso
SwePeso
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5171 Visits: 3433

This is an interesting approach!

However, since float is not exact value there might be a difference in result. But since we only want to keep day information that might not be a problem.

If you have the time, run your test-code again with this code

select cast(cast(getdate() as int) as datetime)




N 56°04'39.16"
E 12°55'05.25"
Razvan Socol
Razvan Socol
SSC Eights!
SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)SSC Eights! (967 reputation)

Group: General Forum Members
Points: 967 Visits: 129

Peter, the following code:
select cast(cast(getdate() as int) as datetime)
will give incorrect results if the time is in the afternoon.

However, the code that Simon wrote:
select cast(floor(cast(getdate() as float)) as datetime)
gives the correct result.

Razvan





Jesper-244176
Jesper-244176
SSC Eights!
SSC Eights! (936 reputation)SSC Eights! (936 reputation)SSC Eights! (936 reputation)SSC Eights! (936 reputation)SSC Eights! (936 reputation)SSC Eights! (936 reputation)SSC Eights! (936 reputation)SSC Eights! (936 reputation)

Group: General Forum Members
Points: 936 Visits: 33

I believe the following function is faster than all the above suggested:

CREATE FUNCTION dbo.fnSeqDates
(
@LowDate DATETIME,
@HighDate DATETIME
)
RETURNS @Dates TABLE
(
SeqDate DATETIME
)
AS
BEGIN

DECLARE @Temp DATETIME
DECLARE @NumberOfDays int

IF @LowDate > @HighDate
SELECT @Temp = @LowDate,
@LowDate = DATEADD(day, DATEDIFF(day, 0, @HighDate) - 1, 0),
@HighDate = DATEADD(day, DATEDIFF(day, 0, @Temp), 0)
ELSE
SELECT @LowDate = DATEADD(day, DATEDIFF(day, 0, @LowDate) - 1, 0),
@HighDate = DATEADD(day, DATEDIFF(day, 0, @HighDate), 0)

SELECT @NumberOfDays = DATEDIFF(d, @LowDate, @HighDate)

INSERT @Dates
SELECT DATEADD(d, Number, @LowDate) AS Date FROM Numbers WHERE Number <= @NumberOfDays

RETURN
END
GO

It uses a Numbers table, containing all Numbers less than, say 1000000. This can be created once and for all as follows:


SELECT TOP 1000000 Number = IDENTITY(INT, 1, 1) INTO Numbers
FROM
sysobjects a1
CROSS JOIN
sysobjects a2
CROSS JOIN
sysobjects a3
CROSS JOIN
sysobjects a4
CROSS JOIN
sysobjects a5


ALTER TABLE Numbers
ADD CONSTRAINT Index_Numbers PRIMARY KEY CLUSTERED(Number)
GO


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search