Number of weeks between two dates

Question

Number of weeks between two dates

Viewing 4 posts - 16 through 18 (of 18 total)

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

MMartin1 One Orange Chip Points: 27799 More actions · Answer 1

Jacob Wilkins (12/14/2016)
MMartin1 (12/14/2016)
James Tran (12/13/2016)
Something like this will return the number of weeks and remaining days + remaining work days.
CREATE FUNCTION [dbo].udfGetWeeksAndDays
(
@StartDate DATETIME
,@EndDate DATETIME
)
RETURNS
TABLE
AS
RETURN
(
with cte as (
SELECT
TotalDays = DATEDIFF(dd, @StartDate, @EndDate) + 1
,TotalWeeks = (DATEDIFF(dd, @StartDate, @EndDate) + 1) / 7
,RemainDays = (DATEDIFF(dd, @StartDate, @EndDate) + 1) % 7
)
select
*
,RemainWorkDays = case when RemainDays > 5 then 5 else RemainDays end
From cte
)
GO
If you look at this portion of your code
,TotalWeeks = (DATEDIFF(dd, @StartDate, @EndDate) + 1) / 7
it fails when the end date is less than the start date.
Compare these two results
declare @startDate date, @endDate date;
select @startDate = '20161212', @endDate = '20161205'
select
yourFormula= (DATEDIFF(dd, @StartDate, @EndDate) + 1) / 7
,myFormula = DATEDIFF(wk, @StartDate, @EndDate)
And see how your formula produces the wrong result.
Eh, it all depends on the requirements.
Just using DATEDIFF(wk...) means that a start of 20161217 and an end of 20161218 will return 1 week, since it's just counting boundaries (which with DATEDIFF(wk...) is always Sunday, regardless of DATEFIRST settings).
That might be desired, or it might not be.
The desired logic and results would have to be spelled out precisely before picking a particular solution.
Cheers!

The difference returned between 12/12 to 12/5 (going backwards) is -1 and not 0. It cant depend.

Also "Weeks" has a specific meaning in SQL Server. So in your example the two dates do traverse one week.

Yes the results wanted could have been better spelled out here.

----------------------------------------------------

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 2

Of course DATEDIFF(wk...) has a specific meaning in SQL Server, but I don't think anyone disagrees with that.

My point is that when someone asks in English "Hey, how can I find the number of weeks between two dates in SQL Server?" it's far from clear that they just want to count the number of week boundaries between the two dates.

If I had to guess, my guess would be that most often that is NOT what they want, but the exact distribution of desires in the real world isn't the main point anyway 🙂

Either way, it's not like I'm saying yours is wrong or his is correct (especially for the negative case, where the other suggestion obviously doesn't work); I'm just pointing out for people who stumble across this thread in the future that it's not at all clear that DATEDIFF(wk...) will satisfy the requirements for the natural language question "How many weeks between these two dates?".

It'll depend on exactly what they're trying to do.

That's what I'm saying, nothing more, nothing less 🙂

Cheers!

MMartin1 One Orange Chip Points: 27799 More actions · Answer 3

I'm with you there 🙂 . The question is a ambiguous as "week" can be interpreted differently. Still had to point out , for anyone who may be looking to the solution posted by the formula I picked out (IE.. one interpretation of "week"),the bug they can run into with that formula.

----------------------------------------------------

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 4

Rem70Rem (1/7/2011)
Well I found out that datediff seems to do what I was looking for.
Thanks all for your help. Appreciated.

An essential part for the possible learning process for others that may read this thread is contained in your original post. It's incredibly rude and in very bad form to delete it like you did.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)