• DaveK2014 (3/4/2015)


    I have a query that I need to run but I need it to look at Date A and if the month of Date A is last month it will be returned. The problem comes when I hit January of next year will it go back to December of the year before or think it should be December of the current year?

    So when the report is ran on January 15 2016 it needs to return all the results from December 2015.

    Thanks

    The right way to do this (avoiding all the year/month date-part manipulation in the process) in all cases is to use what some people call a "Closed-Open Interval" or "Half Open Interval" where the start date is inclusive and the end date is exclusive. The generally accepted standard is to do something like this for your reporting queries.

    SELECT columnlist

    FROM dbo.YourTable

    WHERE SomeDateTimeColumn >= DATEADD(mm,DATEDIFF(mm,0,CURRENT_TIMESTAMP),-1) --Beginning of previous month (inclusive)

    AND SomeDateTimeColumn < DATEADD(mm,DATEDIFF(mm,0,CURRENT_TIMESTAMP), 0) --Beginning of current month (exclusive)

    ;

    The code above will always return the previous month's data based on whatever the current date is even when the year changes.

    The short explanation of the code is that the "0's" are shorthand for '1900-01-01' (base date) and "-1" is shorthand for '1899-12-31'. The DATEDIFFs count the number of monthly boundaries crossed since the base date and the DATEADDs convert that count of boundaries back to a date which will always be the first of the month for whatever month is returned. It won't work for dates before 1900 but it will work for all dates from 1900-01-01 thru 9999-12-31.

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