December 14, 2016 at 11:54 am
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) / 7it 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.
----------------------------------------------------
December 14, 2016 at 12:12 pm
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!
December 14, 2016 at 12:59 pm
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.
----------------------------------------------------
December 14, 2016 at 7:38 pm
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
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply