

SSCrazy
Group: General Forum Members
Last Login: Tuesday, April 14, 2015 6:45 AM
Points: 2,403,
Visits: 3,431





SSCAddicted
Group: General Forum Members
Last Login: Wednesday, September 14, 2016 10:54 AM
Points: 444,
Visits: 422


Nice article, alternatively... SET DATEFIRST 1  force monday as the beginning of the week DECLARE @bdy datetime, @RetiralAge smallint SET @bdy='24jul1971' SET @RetiralAge=65
SELECT datediff (dd,getdate()+(8datepart(dw,getdate())),dateadd(year,@RetiralAge,@bdy))/7 as "Mondays till you retire"
Ally




SSCrazy
Group: General Forum Members
Last Login: Tuesday, April 14, 2015 6:45 AM
Points: 2,403,
Visits: 3,431


True, and it works too. But maybe not that versatile?
N 56°04'39.16" E 12°55'05.25"




SSCAddicted
Group: General Forum Members
Last Login: Friday, June 17, 2011 6:28 AM
Points: 422,
Visits: 33


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...




SSCrazy
Group: General Forum Members
Last Login: Tuesday, April 14, 2015 6:45 AM
Points: 2,403,
Visits: 3,431


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"




SSCAddicted
Group: General Forum Members
Last Login: Friday, June 17, 2011 6:28 AM
Points: 422,
Visits: 33


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.




SSCrazy
Group: General Forum Members
Last Login: Tuesday, April 14, 2015 6:45 AM
Points: 2,403,
Visits: 3,431


Ok, ok ok Still don't get proper result with date range 18991229 and 19000103. Running select datediff(d, '19000101', '19000103')/7  datediff(d, '19000102', '18991229')/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"




SSCAddicted
Group: General Forum Members
Last Login: Friday, June 17, 2011 6:28 AM
Points: 422,
Visits: 33


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', '19000103')/7  datediff(d, '18000107', '18991229')/7 gives you one Monday. My formula needs two reference dates, a Monday (e.g. 19000101 or 18000106) and the following Tuesday (e.g. 19000102 or 18000107). Both @dateFrom and @dateTo must be larger than these days for the formula to work. But OK, you found the weak point




Say Hey Kid
Group: General Forum Members
Last Login: Wednesday, November 25, 2015 7:48 AM
Points: 693,
Visits: 129


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




Say Hey Kid
Group: General Forum Members
Last Login: Wednesday, November 25, 2015 7:48 AM
Points: 693,
Visits: 129


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



