get number of days from two dates

Question

get number of days from two dates

Viewing 3 posts - 16 through 17 (of 17 total)

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

jared-709193 (9/10/2011)
I think that in this case the RBAR is okay. This is a small query and is only doing the select and inserts. Although I agree it is best to work in sets, I see no problem with my approach from a practical standpoint as it is easy for anyone to look at and see what I am doing and it does not affect performance. Just as denormalizing a database has its place, so do while loops.
Jared

True enough... the problem is that if you use RBAR to solve such small problems, you're continuing to practice RBAR methods. It's like practicing the piano... unless someone is a piano comedian, it's a bad idea to practice hitting the wrong keys.

I also try to not justify code based on the few number of rows it may have to handle for a given problem because someone who doen't know better will pick it up and use it on a much larger problem where it will be a performance problem.

--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)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Jeff Moden (9/9/2011)
SQLRNNR (9/8/2011)
jared-709193 (9/8/2011)
SQLRNNR (9/8/2011)
Here's a script that should work for you.
SET DATEFORMAT DMY
DECLARE @StartDateDATE = '05/06/2011'
,@EndDateDATE = '31/08/2012'
;
WITH getmonths AS (
SELECT cast(Convert(varchar,'01/' + cast(MonthNum as varchar) + '/' + cast(yr.yr as varchar)) as Date) as Moy
FROM ( VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) Mnths (MonthNum)
Cross Apply (Select Year(@StartDate) as yr
Union
Select Year(@EndDate) as yr
) yr
), inputdates as (
Select dates from (values (@StartDate),(@EndDate)) D (Dates)
)
select DateName(m,g.moy) as MonthInRange,year(g.moy) as YrInRange
,DatePart(d,dateadd(mm, datediff(mm, 0, g.moy)+1, 0)-1)
- Case When datepart(d,id.Dates) = DatePart(d,dateadd(mm, datediff(mm, 0, g.moy)+1, 0)-1)
Then 0
When datepart(d,g.moy) < datepart(d,id.Dates)
Then datepart(d,id.Dates)
Else Isnull(datepart(d,id.Dates),datepart(d,g.moy)-1)
End as DaysInMonthInRange
From getmonths g
Left Outer Join inputdates Id
On datepart(m,g.moy) = datepart(m,id.Dates)
And year(g.moy) = year(id.Dates)
Where g.moy between @StartDate and @EndDate
Or id.Dates between @StartDate and @EndDate
I prefer to stay away from converting to varchar whenever dealing with dates if I can. Interesting approach though! Thanks!
Jared
Notice that is only in the pseudo-numbers table created at the beginning for string manipulation. It is immediately put into Date format in the same step.
Nicely done, Jason, but it only works for two years max.

I knew I should have tried one more year to test.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Jeff Moden (9/9/2011)
I took the minimalist approach which also avoids the loop which would allow it to be used in an iTVF (unlike looping solutions). You might be able to speed it up a bit if the Tally Table were replaced by an "Itzik Style" cascading cteTally, instead. This is good for a domain of more than 900 years and it also works in 2005.
SET DATEFORMAT DMY
DECLARE @StartDate DATETIME,
@EndDate DATETIME
;
SELECT @StartDate = '05/06/2011',
@EndDate = '31/08/2012'
;
WITH
cteMonthEnd AS
(
SELECT t.N,
MonthEnd = DATEADD(mm,DATEDIFF(mm,0,@StartDate) + t.N, 0) - 1
FROM dbo.Tally t
WHERE t.N BETWEEN 1 AND DATEDIFF(mm,@StartDate,@EndDate) + 1
)
SELECT MonthEnd,
Days = DATEPART(dd,MonthEnd) - CASE WHEN N > 1 THEN 0 ELSE DATEPART(dd,@StartDate) END
FROM cteMonthEnd
;
If you don't know what a Tally Table (or cteTally) is or how it works to replace many types of While Loops, please see the following article...
http://www.sqlservercentral.com/articles/T-SQL/62867/

Much better approach - imo.

Here is what it would look like with the Itzik Style numbers table.

SET DATEFORMAT DMY

DECLARE @StartDate DATETIME,

@EndDate DATETIME

;

SELECT @StartDate = '05/06/2011',

@EndDate = '31/08/2013'

;

WITH Nbrs_2( n ) AS (SELECT 1 UNION all

SELECT 1 UNION all

SELECT 0),

Nbrs_3( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 CROSS JOIN Nbrs_2 n3)

, tally (n) as (SELECT ROW_NUMBER() OVER (ORDER BY n)

FROM Nbrs_3

)

,cteMonthEnd AS

(

SELECT t.N,

MonthEnd = DATEADD(mm,DATEDIFF(mm,0,@StartDate) + t.N, 0) - 1

FROM Tally t

WHERE t.N BETWEEN 1 AND DATEDIFF(mm,@StartDate,@EndDate) + 1

)

SELECT MonthEnd,

Days = DATEPART(dd,MonthEnd) - CASE WHEN N > 1 THEN 0 ELSE DATEPART(dd,@StartDate) END

FROM cteMonthEnd

;

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events