Group By week ending date 2

[ESquared]

Anyone have a better idea than this?

-- where dt is a date column,
SELECT weekendingdate = convert(datetime,convert(int,floor(convert(bigint,convert(varbinary,dt)) / 4294967296)) / 7 * 7 +6)
FROM Table

I am considering Sunday the week-ending date.

Results:

[tt]dt weekendingdate
2004/07/05 2004/07/11
2004/07/06 2004/07/11
2004/07/07 2004/07/11
2004/07/08 2004/07/11
2004/07/09 2004/07/11
2004/07/10 2004/07/11
2004/07/11 2004/07/11
2004/07/12 2004/07/18
2004/07/13 2004/07/18
2004/07/14 2004/07/18
2004/07/15 2004/07/18
2004/07/16 2004/07/18
2004/07/17 2004/07/18[/tt]

 

[NathanGriffiths]

First make sure Monday is set as the first day of the week (the default is Sunday)

set datefirst 1

Then you can use these date function to calculate the last day of the week;

select dateadd(dd, 7 - datepart(dw, <date>), <date>)

HTH

Nathan

----------------------------------------
Want to get a good response to your question? Read this FAQ! -> faq183-874
----------------------------------------

 

[ESquared]

duh.

 

[ESquared]

And what's your recommendation for truncating the time portion of a datetime? I neglected to attach times to the dates in my example...

 

[ESquared]

I guess I'll just use this: Convert(varchar(8),[expression],112)