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
instead. Note that
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)
SELECT DATEADD(dd, @TotalRows, d.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)
You must be logged in to reply to this topic. Login to reply