Log in  ::  Register  ::  Not logged in

## How many more Mondays until I retire?

Author
Message
John Rempel
SSC Eights!

Group: General Forum Members
Points: 951 Visits: 286
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
SSCrazy

Group: General Forum Members
Points: 2853 Visits: 3431

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
SSCrazy

Group: General Forum Members
Points: 2853 Visits: 3431

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
Grasshopper

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

Group: General Forum Members
Points: 169 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   RETURNENDgo`

Fal
Mr or Mrs. 500

Group: General Forum Members
Points: 528 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:
 iterations datediff floor 1 0 0 10 0 0 100 0 0 1000 7 6 10000 68 75 100000 712 810
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
Right there with Babe

Group: General Forum Members
Points: 737 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):

 Peter Razvan R2ro 1906 1670 766 2076 2846 796 830 1653 876 906 610 766 860 1703 873 3170 2846 703

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

Razvan

SwePeso
SSCrazy

Group: General Forum Members
Points: 2853 Visits: 3431

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
Right there with Babe

Group: General Forum Members
Points: 737 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

Group: General Forum Members
Points: 444 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