# How many more Mondays until I retire?

• Comments posted to this topic are about the item How many more Mondays until I retire?

N 56°04'39.16"
E 12°55'05.25"

• Nice article,  alternatively...

SET DATEFIRST 1 -- force monday as the beginning of the week

DECLARE @bdy datetime,

@RetiralAge smallint

SET @bdy='24-jul-1971'

SET @RetiralAge=65

SELECT  datediff (dd,getdate()+(8-datepart(dw,getdate())),dateadd(year,@RetiralAge,@bdy))/7  as "Mondays till you retire"

-Ally

• True, and it works too. But maybe not that versatile?

N 56°04'39.16"
E 12°55'05.25"

• Calculating the number of Mondays between two dates was the subject of the following thread:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=258968

My suggestion was the following:

select datediff(d, '19000101', @dateTo)/7 - datediff(d, '19000102', @dateFrom)/7

I still believe this is the fastest method to calculate the number of Mondays between two dates. On the other hand, it is not very flexible...

• Yes, it works well if not more than one of the two dates already is a monday. Using this code

select (datediff(d, '20060213', '20060724') + 1)/7 - datediff(d, '20060213', '20060717')/7

reports 1 monday to me, where it should report 2 mondays.

But your query is very fast.

N 56°04'39.16"
E 12°55'05.25"

• No, no, use the formula above:

select datediff(d, '19000101', '20060724')/7 - datediff(d, '19000102', '20060717')/7

returns 2, i.e 2 Mondays between the 17th and the 24th.

• Ok, ok ok

Still don't get proper result with date range 1899-12-29 and 1900-01-03.

Running

select datediff(d, '19000101', '1900-01-03')/7 - datediff(d, '19000102', '1899-12-29')/7

gives me 0 mondays. Shouldn't it report 1 monday for January 1, 1900?

N 56°04'39.16"
E 12°55'05.25"

• Do you really need dates that old?

You could use

select datediff(d, '18000106', @dateTo)/7 - datediff(d, '18000107', @dateFrom)/7

select datediff(d, '18000106', '1900-01-03')/7 - datediff(d, '18000107', '1899-12-29')/7

gives you one Monday.

My formula needs two reference dates, a Monday (e.g. 1900-01-01 or 1800-01-06) and the following Tuesday (e.g. 1900-01-02 or 1800-01-07). Both @dateFrom and @dateTo must be larger than these days for the formula to work. But OK, you found the weak point

• In the first code sample, I suppose that this portion should return each number between 0 and 32, right ?

SELECT

a.i + b.j + c.k

FROM

(SELECT 0 i UNION ALL SELECT 1 UNION ALL SELECT 2) a,

(SELECT 0 j UNION ALL SELECT 3 UNION ALL SELECT 6) b,

(SELECT 0 k UNION ALL SELECT 12 UNION ALL SELECT 24) c

ORDER BY 1

Well... it doesn't. It generate some numbers between 0 and 32, but not all of them. The above query only generates 27 values. If you want to generate each number between 0 and 31, you can use this query:

SELECT a.i + b.j + c.k

FROM

(SELECT 0 i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) a,

(SELECT 0 j UNION ALL SELECT 4 UNION ALL SELECT 8 UNION ALL SELECT 12) b,

(SELECT 0 k UNION ALL SELECT 16) c

ORDER BY 1

Razvan

• I modified your function as follows:

CREATE

FUNCTION dbo.MySeqDates (@LowDate DATETIME, @HighDate DATETIME)

RETURNS @Dates TABLE (SeqDate DATETIME)

AS

BEGIN

DECLARE @Temp DATETIME

IF @LowDate > @HighDate

SELECT @Temp = @LowDate,

@LowDate = DATEADD(day, DATEDIFF(day, 0, @HighDate), 0),

@HighDate = DATEADD(day, DATEDIFF(day, 0, @Temp), 0)

ELSE

SELECT @LowDate = DATEADD(day, DATEDIFF(day, 0, @LowDate), 0),

@HighDate = DATEADD(day, DATEDIFF(day, 0, @HighDate), 0)

INSERT @Dates (SeqDate) VALUES (@LowDate)

DECLARE @TotalRows int, @RowCnt int

SET @TotalRows=1

SET @RowCnt=1

WHILE @RowCnt > 0 BEGIN

INSERT @Dates (SeqDate)

FROM @Dates d

WHERE DATEADD(dd, @TotalRows, d.SeqDate) <= @HighDate

SET @RowCnt=@@ROWCOUNT

SET @TotalRows=@TotalRows+@RowCnt

END

RETURN

END

The difference is that I use a variable to count the number of rows (instead of using COUNT() in a derived table) which should be faster if the number of rows is really big. To benchmark, try running something like this:

DECLARE

@t datetime

SET @t=GETDATE()

SELECT COUNT(*) FROM dbo.fnSeqDates('20060101','23070131')

PRINT CONVERT(varchar(10),DATEDIFF(ms,@t,GETDATE()))+' ms'

SET @t=GETDATE()

SELECT COUNT(*) FROM dbo.MySeqDates('20060101','23070131')

PRINT CONVERT(varchar(10),DATEDIFF(ms,@t,GETDATE()))+' ms'

Of course, it's very unlikely that anyone would need such a long period (and for shorter periods the difference in performance is very small).

Razvan

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

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

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

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

• 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

Viewing 15 posts - 1 through 15 (of 27 total)