Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to find the 2nd Monday following a given date. Expand / Collapse
Author
Message
Posted Thursday, August 8, 2013 10:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 28, 2014 2:51 PM
Points: 45, Visits: 236
Can someone help me figure this problem out? Thanks
Post #1482444
Posted Thursday, August 8, 2013 10:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 7, 2013 4:04 PM
Points: 42, Visits: 107
How about this?

set datefirst 1

declare @basedate as datetime

set @basedate = '20130808'

select
Today = @basedate,
NextMonday = dateadd(dd, 8-datepart(dw, @basedate), @basedate),
MondayAfterNext = dateadd(dd, 15-datepart(dw, @basedate), @basedate)

Happy Coding!!!


~~ CK
Post #1482454
Posted Thursday, August 8, 2013 10:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 28, 2014 2:51 PM
Points: 45, Visits: 236
Thank you. That works beautifully. Can you tell me what "set datefirst 1" is used for? It doesn't change anything if I remove it.
Post #1482466
Posted Thursday, August 8, 2013 11:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 7, 2013 4:04 PM
Points: 42, Visits: 107
To make sure first day of the week is Monday so you can assume that, to get the next Monday, just add 8 minus the current day of the week. Monday after next can be calculated by adding 15 minus current day of the week.

Here's the full description from BOL.
Post #1482490
Posted Thursday, August 8, 2013 11:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 28, 2014 2:51 PM
Points: 45, Visits: 236
Thanks
Post #1482492
Posted Sunday, August 11, 2013 12:54 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 12:48 PM
Points: 2,397, Visits: 3,406
Remember that SET DATEFIRST will recompile your code. And also, not everyone has DATEFIRST 1 as default setting.

Try this code that is safe and doesn't rely on any setting and also, if doesn't have the annoying time portion.

SELECT	GETDATE() AS Today,
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000108') AS NextMonday,
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000115') AS MondayAfterNext




N 56°04'39.16"
E 12°55'05.25"
Post #1483076
Posted Monday, August 12, 2013 11:16 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:30 PM
Points: 3,627, Visits: 5,273
SwePeso (8/11/2013)
Remember that SET DATEFIRST will recompile your code. And also, not everyone has DATEFIRST 1 as default setting.

Try this code that is safe and doesn't rely on any setting and also, if doesn't have the annoying time portion.

SELECT	GETDATE() AS Today,
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000108') AS NextMonday,
DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000115') AS MondayAfterNext



Isn't this more or less the same thing with a little less math?

SELECT Today=GETDATE()
,NextMonday=DATEADD(week, 1+DATEDIFF(week, 0, GETDATE()), 0)
,MondayAfterNext=DATEADD(week, 2+DATEDIFF(week, 0, GETDATE()), 0)





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1483562
Posted Tuesday, August 13, 2013 12:13 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 12:48 PM
Points: 2,397, Visits: 3,406
Using WEEK as calculation is prone to at least two errors. In this case, if you are using a sunday you will get wrong result.
See this repro
DECLARE	@Basedate DATETIME = '20130811';

SET DATEFIRST 1;

SELECT @@DATEFIRST AS [Current SET DATEFIRST setting],
DATEADD(week, 1+DATEDIFF(week, 0, @Basedate), 0) AS [NextMonday by Dwain.c],
DATEADD(week, 2+DATEDIFF(week, 0, @Basedate), 0) AS [MondayAfterNext by Dwain.c],
dateadd(dd, 8-datepart(dw, @Basedate), @Basedate) AS [NextMonday by ck9663],
dateadd(dd, 15-datepart(dw, @Basedate), @Basedate) AS [MondayAfterNext by ck9663],
DATEADD(DAY, DATEDIFF(DAY, '19000101', @Basedate) / 7 * 7, '19000108') AS [NextMonday by SwePeso],
DATEADD(DAY, DATEDIFF(DAY, '19000101', @Basedate) / 7 * 7, '19000115') AS [MondayAfterNext by SwePeso]

SET DATEFIRST 2;

SELECT @@DATEFIRST AS [Current SET DATEFIRST setting],
DATEADD(week, 1+DATEDIFF(week, 0, @Basedate), 0) AS [NextMonday by Dwain.c],
DATEADD(week, 2+DATEDIFF(week, 0, @Basedate), 0) AS [MondayAfterNext by Dwain.c],
dateadd(dd, 8-datepart(dw, @Basedate), @Basedate) AS [NextMonday by ck9663],
dateadd(dd, 15-datepart(dw, @Basedate), @Basedate) AS [MondayAfterNext by ck9663],
DATEADD(DAY, DATEDIFF(DAY, '19000101', @Basedate) / 7 * 7, '19000108') AS [NextMonday by SwePeso],
DATEADD(DAY, DATEDIFF(DAY, '19000101', @Basedate) / 7 * 7, '19000115') AS [MondayAfterNext by SwePeso]

SET DATEFIRST 3;

SELECT @@DATEFIRST AS [Current SET DATEFIRST setting],
DATEADD(week, 1+DATEDIFF(week, 0, @Basedate), 0) AS [NextMonday by Dwain.c],
DATEADD(week, 2+DATEDIFF(week, 0, @Basedate), 0) AS [MondayAfterNext by Dwain.c],
dateadd(dd, 8-datepart(dw, @Basedate), @Basedate) AS [NextMonday by ck9663],
dateadd(dd, 15-datepart(dw, @Basedate), @Basedate) AS [MondayAfterNext by ck9663],
DATEADD(DAY, DATEDIFF(DAY, '19000101', @Basedate) / 7 * 7, '19000108') AS [NextMonday by SwePeso],
DATEADD(DAY, DATEDIFF(DAY, '19000101', @Basedate) / 7 * 7, '19000115') AS [MondayAfterNext by SwePeso]

SET DATEFIRST 4;

SELECT @@DATEFIRST AS [Current SET DATEFIRST setting],
DATEADD(week, 1+DATEDIFF(week, 0, @Basedate), 0) AS [NextMonday by Dwain.c],
DATEADD(week, 2+DATEDIFF(week, 0, @Basedate), 0) AS [MondayAfterNext by Dwain.c],
dateadd(dd, 8-datepart(dw, @Basedate), @Basedate) AS [NextMonday by ck9663],
dateadd(dd, 15-datepart(dw, @Basedate), @Basedate) AS [MondayAfterNext by ck9663],
DATEADD(DAY, DATEDIFF(DAY, '19000101', @Basedate) / 7 * 7, '19000108') AS [NextMonday by SwePeso],
DATEADD(DAY, DATEDIFF(DAY, '19000101', @Basedate) / 7 * 7, '19000115') AS [MondayAfterNext by SwePeso]

SET DATEFIRST 5;

SELECT @@DATEFIRST AS [Current SET DATEFIRST setting],
DATEADD(week, 1+DATEDIFF(week, 0, @Basedate), 0) AS [NextMonday by Dwain.c],
DATEADD(week, 2+DATEDIFF(week, 0, @Basedate), 0) AS [MondayAfterNext by Dwain.c],
dateadd(dd, 8-datepart(dw, @Basedate), @Basedate) AS [NextMonday by ck9663],
dateadd(dd, 15-datepart(dw, @Basedate), @Basedate) AS [MondayAfterNext by ck9663],
DATEADD(DAY, DATEDIFF(DAY, '19000101', @Basedate) / 7 * 7, '19000108') AS [NextMonday by SwePeso],
DATEADD(DAY, DATEDIFF(DAY, '19000101', @Basedate) / 7 * 7, '19000115') AS [MondayAfterNext by SwePeso]

SET DATEFIRST 6;

SELECT @@DATEFIRST AS [Current SET DATEFIRST setting],
DATEADD(week, 1+DATEDIFF(week, 0, @Basedate), 0) AS [NextMonday by Dwain.c],
DATEADD(week, 2+DATEDIFF(week, 0, @Basedate), 0) AS [MondayAfterNext by Dwain.c],
dateadd(dd, 8-datepart(dw, @Basedate), @Basedate) AS [NextMonday by ck9663],
dateadd(dd, 15-datepart(dw, @Basedate), @Basedate) AS [MondayAfterNext by ck9663],
DATEADD(DAY, DATEDIFF(DAY, '19000101', @Basedate) / 7 * 7, '19000108') AS [NextMonday by SwePeso],
DATEADD(DAY, DATEDIFF(DAY, '19000101', @Basedate) / 7 * 7, '19000115') AS [MondayAfterNext by SwePeso]

SET DATEFIRST 7;

SELECT @@DATEFIRST AS [Current SET DATEFIRST setting],
DATEADD(week, 1+DATEDIFF(week, 0, @Basedate), 0) AS [NextMonday by Dwain.c],
DATEADD(week, 2+DATEDIFF(week, 0, @Basedate), 0) AS [MondayAfterNext by Dwain.c],
dateadd(dd, 8-datepart(dw, @Basedate), @Basedate) AS [NextMonday by ck9663],
dateadd(dd, 15-datepart(dw, @Basedate), @Basedate) AS [MondayAfterNext by ck9663],
DATEADD(DAY, DATEDIFF(DAY, '19000101', @Basedate) / 7 * 7, '19000108') AS [NextMonday by SwePeso],
DATEADD(DAY, DATEDIFF(DAY, '19000101', @Basedate) / 7 * 7, '19000115') AS [MondayAfterNext by SwePeso]




N 56°04'39.16"
E 12°55'05.25"
Post #1483572
Posted Tuesday, August 13, 2013 12:24 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:30 PM
Points: 3,627, Visits: 5,273
Interesting results SWEPESO. Didn't think of checking Sunday.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1483579
Posted Tuesday, August 13, 2013 12:27 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 12:48 PM
Points: 2,397, Visits: 3,406
No problem.



N 56°04'39.16"
E 12°55'05.25"
Post #1483580
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse