Selecting list of month numbers between two dates

Question

Selecting list of month numbers between two dates

preetha.sathyamoorthy

Ten Centuries

Points: 1180
More actions
May 3, 2008 at 4:42 am

#187213

Hi,
I Have to get all the month numbers inbetween two dates
I tried the Function DateDiff(Month,'1/3/2000','1/6/2000')
it gives the Difference in months ,but i want all the months inbetween to be listed
Awaiting your Reply
Regards,
Preetha

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

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

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

I'm not sure what you mean by find all "month numbers" or "i want all the months inbetween to be listed"... do you want to find the first of every month in the date range or do you want to find all days for all months in the date range or ???

This script finds the first of every month in the date range. Remove any columns from the outer SELECT that you don't need...

--=======================================================================================

-- Find first of every month between the start and end dates (inclusive)

--=======================================================================================

--===== Here are the two parameters you wanted

DECLARE @DateStart DATETIME

DECLARE @DateEnd DATETIME

SELECT @DateStart = '20070429',

@DateEnd = '20081201'

--===== Find the dates using a Tally table as a counter.

-- The outer select formats it. Once cached, it's incredibly fast.

;WITH

cteTally AS

(--==== Returns a value of 1 to the number of months in date range

SELECT TOP (DATEDIFF(mm,

DATEADD(mm,DATEDIFF(mm,0,@DateStart),0), --First of start month

DATEADD(mm,DATEDIFF(mm,0,@DateEnd)+1,0))) --First of month after end month

N = ROW_NUMBER() OVER (ORDER BY t1.Object_ID)

FROM Master.sys.All_Columns t1

CROSS JOIN Master.sys.All_Columns t2

)

SELECT N,

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

NextStart = DATEADD(mm,DATEDIFF(mm,0,@DateStart)+t.N,0)

FROM cteTally t

This one finds all the dates of all months in the date range... again, remove any columns from the outer SELECT that you don't need.

--=======================================================================================

-- Find all dates for every month between the start and end dates (inclusive)

--=======================================================================================

--===== Here are the two parameters you wanted

DECLARE @DateStart DATETIME

DECLARE @DateEnd DATETIME

SELECT @DateStart = '20070429',

@DateEnd = '20081201'

--===== Find the dates using a Tally table as a counter.

-- The outer select formats it. Once cached, it's incredibly fast.

;WITH

cteTally AS

(--==== Returns a value of 1 to the number of days of all months in date range

SELECT TOP (DATEDIFF(dd,

DATEADD(mm,DATEDIFF(mm,0,@DateStart),0), --First of start month

DATEADD(mm,DATEDIFF(mm,0,@DateEnd)+1,0))) --First of month after end month

N = ROW_NUMBER() OVER (ORDER BY t1.Object_ID)

FROM Master.sys.All_Columns t1

CROSS JOIN Master.sys.All_Columns t2

)

SELECT N,

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

NextStart = DATEADD(mm,DATEDIFF(mm,0,@DateStart),0)+t.N

FROM cteTally t

Last, but not least, this one just finds all dates in the date range without regard to whole months. Again, remove any columns in the outer SELECT that you don't need.

--=======================================================================================

-- Find all dates between the start and end dates (inclusive)

--=======================================================================================

--===== Here are the two parameters you wanted

DECLARE @DateStart DATETIME

DECLARE @DateEnd DATETIME

SELECT @DateStart = '20070429',

@DateEnd = '20081201'

--===== Find the dates using a Tally table as a counter.

-- The outer select formats it. Once cached, it's incredibly fast.

;WITH

cteTally AS

(--==== Returns a value of 1 to the number of days in date range

SELECT TOP (DATEDIFF(dd,

DATEADD(dd,DATEDIFF(dd,0,@DateStart),0), --Whole day start of range

DATEADD(dd,DATEDIFF(dd,0,@DateEnd)+1,0))) --Whole day end of range

N = ROW_NUMBER() OVER (ORDER BY t1.Object_ID)

FROM Master.sys.All_Columns t1

CROSS JOIN Master.sys.All_Columns t2

)

SELECT N,

DateStart = DATEADD(dd,DATEDIFF(dd,0,@DateStart),0)+t.N-1,

NextStart = DATEADD(dd,DATEDIFF(dd,0,@DateStart),0)+t.N

FROM cteTally t

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

preetha.sathyamoorthy Ten Centuries Points: 1180 More actions · Answer 2

Hi Jeff,

Many thanks for your Valuable support and i got the right Idea from your samples and achieved the output as expected.

Thanks a lot,

Regards,

Preetha

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 3

Thank you for the feedback... but we'd all be interestd in what you actually meant...

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

mptetf Valued Member Points: 57 More actions · Answer 4

Excellent code,

using Master.sys.All_Columns to ensure enough rows for iterations is a very useful trick.

Thanks a lot

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 5

You bet... thank you for the feedback.

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

ashish sinha-404083 SSC Veteran Points: 296 More actions · Answer 6

Hi Jeff,

This query is very helpful.

I have one more problem - I have a startdate and enddate, I would like to have number of days in each month between the two dates. Example - startdate = '24-sep-2008' and enddate = '23-sep-2011' then i would like a result set, which would give me

startdate days

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

24-sep-2008 7

01-oct-2008 31

..................

23-sep-2011 23

could you please tell me how can i do that?

Regards,

Ashish

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 7

Use a calendar table

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

Clear Sky SQL
My Blog[/url]

sivakumar.ss SSC Veteran Points: 242 More actions · Answer 8

Hi jeff,

xlnt solution.......

One doubt !

My requirement is like that instead of start date of every month [01/04/2007], i required end date of every month [31/04/2007]

bcoz no. of days in the month may vary as 31 or 30 or 28 or 29.

kindly help me..

very urgent situation for me

Try Try Try Again,
One Day u vl succeed..................

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 9

simply add one month , then minus one day.

Clear Sky SQL
My Blog[/url]

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

sivakumar.ss (3/20/2010)
Hi jeff,
xlnt solution.......
One doubt !
My requirement is like that instead of start date of every month [01/04/2007], i required end date of every month [31/04/2007]
bcoz no. of days in the month may vary as 31 or 30 or 28 or 29.
kindly help me..
very urgent situation for me

Like Dave said, find the first of the month for the given date, then add one month and substract a day. Here's how to do it using GETDATE() as the current datetime value...

SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)-1

--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: 1004407 More actions · Answer 11

BTW... you should never find the last day of the month because you may miss all but the first instant of that last day all together. Instead, you should always find the first of the next month and your criteria should be "greater than or equal 1st of this month and less than 1st of next month".

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

sivakumar.ss SSC Veteran Points: 242 More actions · Answer 12

Thank u Mr.Jeff and Mr.Dave

The solution is really helpful

Try Try Try Again,
One Day u vl succeed..................

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

Thanks for the feedback, sivakumar.

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

sivakumar.ss SSC Veteran Points: 242 More actions · Answer 14

hi jeff,

Help me, that i want the number of days for start date of the month and end date of the month.

This is my query......

--========================= Procedures which list out month along with no. of days============

ALTER PROCEDURE USP_CummInterest

(

@InvDate Datetime ,

@MatDate Datetime

)

AS

BEGIN

;WITH

cteTally AS

(

SELECT TOP (DATEDIFF(mm,

DATEADD(mm,DATEDIFF(mm,0,@InvDate),0), --First of start month

DATEADD(mm,DATEDIFF(mm,0,@MatDate)+1,0) --First of month after end month

)

N = ROW_NUMBER() OVER (ORDER BY t1.Object_ID)

FROM Master.sys.All_Columns t1

)

SELECT

MonthList = N,

NumberOfDays = DATEPART (dd, DATEADD(mm,DATEDIFF(mm,0,@InvDate)+t.N,0)-1 ),

[Month] = DATEADD(mm,DATEDIFF(mm,0,@InvDate)+t.N,0)-1

FROM cteTally t

END

GO

EXEC USP_CummInterest '20081029','20090311'

--================= The OutPut==============

MonthListNumberOfDaysMonth

1312008-10-31 00:00:00.000

2302008-11-30 00:00:00.000

3312008-12-31 00:00:00.000

4312009-01-31 00:00:00.000

5282009-02-28 00:00:00.000

6312009-03-31 00:00:00.000

-- =========================================

Here i want actual days for the first and last month. (Its showing as 31 days).

siva

Try Try Try Again,
One Day u vl succeed..................