Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 How many more Mondays until I retire? Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, June 22, 2006 3:00 PM
 SSCrazy Group: General Forum Members Last Login: Tuesday, April 14, 2015 6:45 AM Points: 2,403, Visits: 3,431
Post #289513
 Posted Wednesday, July 19, 2006 2:40 AM
 SSC-Addicted Group: General Forum Members Last Login: Tuesday, September 27, 2016 3:28 PM Points: 447, Visits: 423
 Nice article,  alternatively...SET DATEFIRST 1 -- force monday as the beginning of the weekDECLARE @bdy datetime, @RetiralAge smallintSET @bdy='24-jul-1971'SET @RetiralAge=65SELECT  datediff (dd,getdate()+(8-datepart(dw,getdate())),dateadd(year,@RetiralAge,@bdy))/7  as "Mondays till you retire"-Ally
Post #295418
 Posted Wednesday, July 19, 2006 3:35 AM
 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"
Post #295426
 Posted Wednesday, July 19, 2006 4:47 AM
 SSC-Addicted 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=258968My suggestion was the following:select datediff(d, '19000101', @dateTo)/7 - datediff(d, '19000102', @dateFrom)/7I 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...
Post #295439
 Posted Wednesday, July 19, 2006 6:00 AM
 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 codeselect (datediff(d, '20060213', '20060724') + 1)/7 - datediff(d, '20060213', '20060717')/7reports 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"
Post #295462
 Posted Wednesday, July 19, 2006 6:07 AM
 SSC-Addicted 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')/7returns 2, i.e 2 Mondays between the 17th and the 24th.
Post #295467
 Posted Wednesday, July 19, 2006 6:35 AM
 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 1899-12-29 and 1900-01-03.Runningselect datediff(d, '19000101', '1900-01-03')/7 - datediff(d, '19000102', '1899-12-29')/7gives me 0 mondays. Shouldn't it report 1 monday for January 1, 1900? N 56°04'39.16"E 12°55'05.25"
Post #295474
 Posted Wednesday, July 19, 2006 7:08 AM
 SSC-Addicted 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 useselect datediff(d, '18000106', @dateTo)/7 - datediff(d, '18000107', @dateFrom)/7instead. Note thatselect datediff(d, '18000106', '1900-01-03')/7 - datediff(d, '18000107', '1899-12-29')/7gives 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
Post #295486
 Posted Wednesday, July 19, 2006 8:13 AM
 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) cORDER BY 1Well... 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) cORDER BY 1Razvan
Post #295517
 Posted Wednesday, July 19, 2006 8:43 AM
 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)ASBEGINDECLARE @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 intSET @TotalRows=1SET @RowCnt=1WHILE @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+@RowCntENDRETURNENDThe 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 datetimeSET @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
Post #295535

 Permissions