Problem with using Datediff in weeks

Question

Post reply

Problem with using Datediff in weeks

Craig Pa

SSC-Addicted

Points: 496
More actions
September 23, 2011 at 6:32 am

#257900

Hi all,
As the subject suggests I am having a problem with using the datediff function in a query I am writing.
Scenario: from any given date I need to work out the number of joiners per week. IE:
Since September 1st 2011 - today
Week | Joiners
1| 5 (1st -7th)
2| 8 (8th – 14th)
3| 2 (15th – 21st)
I understand that when using datediff with a datepart of ww that it calculates the week of the year. This is causing my results to be wrong.
Eg:
DECLARE @start datetime, @end datetime
SET @start = '1 sep 2011'
SET @end = '4 sep 2011'
SELECT datediff(ww,@start,@end)
The results from this return 1 week when in fact it should be a 0 as there are only 3 days.
I have considered using CASE and using a dd datepart but I need this to be dynamic as possible so that the query could be run for any number of weeks.
I would also need to get the results to start from 1 and not 0 as week 1 would be from the start date.
I have looked around on the web site and have not had much success in finding something to help me achieve what I need to do.
Any help would be much appreciated.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Craig Pa SSC-Addicted Points: 496 More actions · Answer 1

sorry i have resolved this by using a dd in the datepart.

DECLARE @start datetime, @end datetime

SET @start = '1 sep 2011'

SET @end = '4 sep 2011'

SELECT (datediff(dd,@start,@end)/7)+1

This post can be deleted unless it is useful.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

It's always useful. Lots of traffic come in here from google.

calvo SSChampion Points: 12930 More actions · Answer 3

This got me looking around for a solution to find calendar week numbers (defined however you'd like but for the sake of this discussion we'll assume a static sun-sat). You said you found a solution just dividing by 7 which makes 9/1 - 9/7 = week 1 and 9/8 - 9/14 = week 2.

This, although a week, is not a static calendar week but a moving time period of 7 days.

Just for fun, I'd like to find a way to return the calendar week number. oooooo! Perhaps a tally table solution?

For example;

9/1 - 9/3 = week 1

9/4 - 9/10 = week 2

9/11 - 9/17 = week 3

9/18 - 9/24 = week 4

9/25 - 9/30 = week 5

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.

calvo SSChampion Points: 12930 More actions · Answer 4

This is what I was able to come up with to find the monthly calendar week number.

DECLARE @dt DATETIME = '9/25/2011'

select DATEDIFF(week,DATEADD(MONTH,DATEDIFF(MONTH,0, @dt),0),@dt)+1

Maybe not the best way, but it works.

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.