August 8, 2013 at 10:11 am
Can someone help me figure this problem out? Thanks
August 8, 2013 at 10:26 am
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
August 8, 2013 at 10:37 am
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.
August 8, 2013 at 11:36 am
Thanks
August 11, 2013 at 12:54 am
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.
SELECTGETDATE() 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"
August 12, 2013 at 11:16 pm
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.
SELECTGETDATE() 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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 13, 2013 at 12:13 am
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"
August 13, 2013 at 12:24 am
Interesting results SWEPESO. Didn't think of checking Sunday.
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 13, 2013 at 12:27 am
No problem.
N 56°04'39.16"
E 12°55'05.25"
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply