get number of days from two dates

Question

Post reply

get number of days from two dates

Nomvula

Hall of Fame

Points: 3778
More actions
September 8, 2011 at 10:42 am

#243517

hi please help, i need to get the number of days for each month e.g i have @StartDate and @EndDate parameter. Whant i need is if the user select the date 05/06/2011 to 31/08/2011 i should get 25,31,31 and not calculate from the begining of the month if the first day of the month is not selected.

Viewing 15 posts - 1 through 15 (of 17 total)

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

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

Use a calendar table.

SELECT COUNT(*), Month FROM dbo.Calendar WHERE dt BETWEEN @Start AND @End GROUP BY Month

Lowell SSC Guru Points: 323518 More actions · Answer 2

ahh the by months things going to be tough...you can't just use the 87 days between them? you have to have it broken down my whatever number of months between?

do you need those months as a string (comma delimited?) or as seperate columns in a row?

--Resuls: 87

select datediff(dd,'2011-06-05','2011-08-31')

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jared SSC Guru Points: 61793 More actions · Answer 3

Start at this page: http://www32.brinkster.com/srisamp/sqlArticles/article_5.htm

The main way for a given month is this: SELECT DAY(DATEADD (m, 1, DATEADD (d, 1 - DAY(GETDATE()), GETDATE())) - 1)

That will give you the amount of days in a given month. So, alter this a bit and it will give you the number of days left in a month from a given date. If you require a full script, I can do it, but I thought teaching a man how to fish is better than providing the fish themselves 🙂

Thanks,

Jared

Jared
CE - Microsoft

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

Here's my calendar table if you need to build one : http://www.sqlservercentral.com/Forums/Attachment8839.aspx

Jared SSC Guru Points: 61793 More actions · Answer 5

Ok, here's what I got that can be put into a stored proc format if needed:

DECLARE @StartDate DATETIME

DECLARE @EndDate DATETIME

DECLARE @numMonths TINYINT

DECLARE @calendarTable TABLE (monthNum TINYINT, monthDays TINYINT)

SET @StartDate = '2011-09-08'

SET @EndDate = '2011-11-09'

SET @numMonths = (SELECT DATEDIFF(m,@StartDate,@EndDate) + 1)

WHILE @numMonths >= 1

BEGIN

INSERT INTO @calendarTable SELECT MONTH(@StartDate), DATEDIFF(d, @startDate, DATEADD(m, 1, DATEADD(d, 1 - DAY(@StartDate), @StartDate)))

SET @numMonths = @numMonths - 1

SET @StartDate = (SELECT DATEADD(m, 1, DATEADD(d, 1 - DAY(@StartDate), @StartDate)))

--SELECT @StartDate

END

SELECT * FROM @calendarTable

Not to shabby 🙂 and useful! EDIT: If you don't want to include day 1 as a day then add SET @StartDate = SELECT DATEADD(d,-1,@StartDate) right before the WHILE.

Thanks,

Jared

Jared
CE - Microsoft

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

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

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

Jared SSC Guru Points: 61793 More actions · Answer 7

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

Jared
CE - Microsoft

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

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.

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

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

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.

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

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

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/

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

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

jared-709193 (9/8/2011)
Start at this page: http://www32.brinkster.com/srisamp/sqlArticles/article_5.htm
The main way for a given month is this: SELECT DAY(DATEADD (m, 1, DATEADD (d, 1 - DAY(GETDATE()), GETDATE())) - 1)
That will give you the amount of days in a given month. So, alter this a bit and it will give you the number of days left in a month from a given date. If you require a full script, I can do it, but I thought teaching a man how to fish is better than providing the fish themselves 🙂
Thanks,
Jared

You're absolutely correct but let's not teach folks how to fish with While Loops, please. 😉

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

Nomvula Hall of Fame Points: 3778 More actions · Answer 12

thanks guys for all the responses really appreciate it and i managed to get it.

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

Nomvula (9/9/2011)
thanks guys for all the responses really appreciate it and i managed to get it.

Considering that there were a couple of problems with some of the code on this thread, you might want to post the code you ended up using just so we can make sure it won't blow up on you somewhere down the line.

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

Jared SSC Guru Points: 61793 More actions · Answer 14

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

Jared
CE - Microsoft