DATEADD issue grouping by week

Question

DATEADD issue grouping by week

blake.hartman

SSC-Addicted

Points: 418
More actions
August 25, 2015 at 9:57 am

#307042

We have bee using something like the following to get the Monday for the specified date.
However, we came across a scenario where this is not working
DECLARE @dt AS datetime
SET @dt = '2/19/2006'
SELECT DATEADD( ww, DATEDIFF( ww, 0, @dt),0)
It had seemed to be working as expected, but we recently came across a problem where The above is returning 2/20, not 2/13

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

blake.hartman (8/25/2015)
We have bee using something like the following to get the Monday for the specified date.
However, we came across a scenario where this is not working
DECLARE @dt AS datetime
SET @dt = '2/19/2006'
SELECT DATEADD( ww, DATEDIFF( ww, 0, @dt),0)
It had seemed to be working as expected, but we recently came across a problem where The above is returning 2/20, not 2/13

Is this the only date? How are you using this at the moment?

blake.hartman SSC-Addicted Points: 418 More actions · Answer 2

We have been using that expression to get the "Week of" date based on the Monday of that week. For some reason though, it recently seems to be returning the following Monday date for Sundays

For example

DECLARE @dt AS datetime

SET @dt = '1/1/2015'

--SELECT DATEADD( ww, DATEDIFF( ww, 0, @dt),0)

SELECT

x.[Date]

,x.WeekOf

FROM

(

SELECT

@dt + t.n AS [Date]

,DATEADD( ww, DATEDIFF( ww, 0, @dt + t.n ),0) AS WeekOf

FROM

a1tbl_Tally t

WHERE

t.n < 8000

) AS x

WHERE

x.[Date] < x.WeekOf

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Actually, this is the expected behavior.

What should it return for the Sunday date?

blake.hartman SSC-Addicted Points: 418 More actions · Answer 4

The previous Monday

So for Sunday Aug 23 2015, it should return Monday Aug 16 2015 instead of Monday Aug 24

blake.hartman SSC-Addicted Points: 418 More actions · Answer 5

Nevermind, I see the problem

It should be this

DATEADD( ww, DATEDIFF( ww, 0, <dateparam>-1 ), 0 )

I thought I was going crazy, but someone copied the wrong grouping expression and I missed the part where it should have had the -1 part

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

blake.hartman (8/25/2015)
Nevermind, I see the problem
It should be this
DATEADD( ww, DATEDIFF( ww, 0, <dateparam>-1 ), 0 )
I thought I was going crazy, but someone copied the wrong grouping expression and I missed the part where it should have had the -1 part

Actually, I'd do it like this:

SELECT DATEADD( ww, DATEDIFF( ww, 0, dateadd(day,-1,@dt)),0)

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 7

Why are you not using the DATEPART function to get the week number?

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

blake.hartman SSC-Addicted Points: 418 More actions · Answer 8

We need the actual date for the "week of" rather than the number and that seemed to be the simplest expression to calc that date

Is there an easier way to calc that using DATEPART?

blake.hartman SSC-Addicted Points: 418 More actions · Answer 9

Lynn Pettis (8/25/2015)
blake.hartman (8/25/2015)
Nevermind, I see the problem
It should be this
DATEADD( ww, DATEDIFF( ww, 0, <dateparam>-1 ), 0 )
I thought I was going crazy, but someone copied the wrong grouping expression and I missed the part where it should have had the -1 part
Actually, I'd do it like this:
SELECT DATEADD( ww, DATEDIFF( ww, 0, dateadd(day,-1,@dt)),0)

It makes sense consistency wise, it should really be written that way

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 10

blake.hartman (8/25/2015)
We need the actual date for the "week of" rather than the number and that seemed to be the simplest expression to calc that date
Is there an easier way to calc that using DATEPART?

Oops, I missed that.

I'd use my Dates dimension to lookup the desired date.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

blake.hartman (8/25/2015)
Lynn Pettis (8/25/2015)
blake.hartman (8/25/2015)
Nevermind, I see the problem
It should be this
DATEADD( ww, DATEDIFF( ww, 0, <dateparam>-1 ), 0 )
I thought I was going crazy, but someone copied the wrong grouping expression and I missed the part where it should have had the -1 part
Actually, I'd do it like this:
SELECT DATEADD( ww, DATEDIFF( ww, 0, dateadd(day,-1,@dt)),0)
It makes sense consistency wise, it should really be written that way

Also, if @dt is declared as DATETIME2 or DATE for instance, this @dt - 1 will fail.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 12

Try this formula:

--DATEADD( wk, DATEDIFF( dd, 0, @dt)/7,0)

SELECT dt, DATEADD( wk, DATEDIFF( dd, 0, dt)/7,0)

FROM (SELECT TOP 30

DATEADD( dd, ROW_NUMBER() OVER(ORDER BY @@SERVERNAME), '20150801')

FROM sys.all_columns) x(dt)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2