May 3, 2008 at 4:42 am
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
May 3, 2008 at 9:53 am
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
Change is inevitable... Change for the better is not.
May 4, 2008 at 10:02 pm
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
May 4, 2008 at 10:12 pm
Thank you for the feedback... but we'd all be interestd in what you actually meant...
--Jeff Moden
Change is inevitable... Change for the better is not.
December 13, 2008 at 9:03 am
Excellent code,
using Master.sys.All_Columns to ensure enough rows for iterations is a very useful trick.
Thanks a lot
December 13, 2008 at 12:07 pm
You bet... thank you for the feedback.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 1, 2009 at 3:46 am
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
July 1, 2009 at 3:56 am
Use a calendar table
March 20, 2010 at 1:20 am
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..................
March 20, 2010 at 1:58 am
simply add one month , then minus one day.
March 21, 2010 at 9:12 pm
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
Change is inevitable... Change for the better is not.
March 21, 2010 at 9:15 pm
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
Change is inevitable... Change for the better is not.
March 21, 2010 at 10:21 pm
Thank u Mr.Jeff and Mr.Dave
The solution is really helpful
Try Try Try Again,
One Day u vl succeed..................
March 21, 2010 at 10:24 pm
Thanks for the feedback, sivakumar.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 22, 2010 at 12:12 am
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..................
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply