Home Forums SQL Server 2012 SQL 2012 - General Somebody Help! - T-SQL syntax help. RE: Somebody Help! - T-SQL syntax help.

  • March 23, 2018 at 7:43 pm

    #1984482

    mw_sql_developer - Friday, March 23, 2018 3:03 PM

    Jeff Moden - Friday, March 23, 2018 2:27 PM

    mw_sql_developer - Friday, March 23, 2018 10:09 AM


    Here is what I need. I have s start date and a end date.
    What I need is to break this down into several intervals ( monthly ) 
    So I was able to just get the very first interval.

    May you please help me get the rest of the intervals please.. I bet there is a stylish way to do this... instead of looping

    Select @VStartDate = '20170110';
    Select @VEndDate = '20171231';

    IF OBJECT_ID('tempdb..#MonthlyIntervals') IS NOT NULL
    DROP TABLE #MonthlyIntervals;

    CREATE TABLE #MonthlyIntervals( StartDt VARCHAR(8) , EndDt VARCHAR(8) );

    --INSERT INTO #MonthlyIntervals( StartDt , EndDt)

    With A as
    (
        Select @VStartDate as StartDt, CONVERT(CHAR(8),   DATEADD(MONTH, DATEDIFF(MONTH, -1, ((DateAdd(MM,1,@VStartDate ))     ))-1, -1), 112 ) as EndDt

    )
    Select * FROM A;

    Given your example dates, what are you expecting for the output?  I ask because it could me any of a different number of outputs based on the dates and your definition of a "monthly" interval.

    Run the code, you will see the output.  What I need is for each month in the span the month start date and end date. works beautifully!

    Here is a method that doesn't rely on string manipulation (slow) - and does not use a predefined Tally table (although - if you have one it would be better):

    Declare @startDate date = '2017-01-10'
      , @endDate date = '2018-12-15';

     With monthlyIntervals (StartDate, EndDate)
      As (
    Select dateadd(day, 1, eomonth(@startDate, t.Number - 1))
      , eomonth(@startDate, t.Number)
     From (Select row_number() over(Order By ac.[object_id]) - 1 As Number From sys.all_columns ac) As t
    Where t.Number <= datediff(month, @startDate, @endDate)
       )
    Select *
     From monthlyIntervals;

    This also uses the date data type - you really should not use strings to represent dates - that will cause all kinds of issues later on...

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs