Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How many more Mondays until I retire?


How many more Mondays until I retire?

Author
Message
SwePeso
SwePeso
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2469 Visits: 3431

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/plarsson/howmanymoremondaysuntiliretire.asp




N 56°04'39.16"
E 12°55'05.25"
Ally MacKenzie
Ally MacKenzie
SSC-Addicted
SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)

Group: General Forum Members
Points: 447 Visits: 426

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


SwePeso
SwePeso
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2469 Visits: 3431

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




N 56°04'39.16"
E 12°55'05.25"
Jesper-244176
Jesper-244176
SSC-Addicted
SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)

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


SwePeso
SwePeso
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2469 Visits: 3431

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"
Jesper-244176
Jesper-244176
SSC-Addicted
SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)

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


SwePeso
SwePeso
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2469 Visits: 3431

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"
Jesper-244176
Jesper-244176
SSC-Addicted
SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)SSC-Addicted (422 reputation)

Group: General Forum Members
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', '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


Razvan Socol
Razvan Socol
Say Hey Kid
Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)

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





Razvan Socol
Razvan Socol
Say Hey Kid
Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)

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





Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search