Selecting list of month numbers between two dates

  • 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

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

  • 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

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

  • Excellent code,

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

    Thanks a lot

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

  • 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

  • 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..................

  • simply add one month , then minus one day.



    Clear Sky SQL
    My Blog[/url]

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

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

  • Thank u Mr.Jeff and Mr.Dave

    The solution is really helpful

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

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

  • 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