Selecting data between just month and year

  • I know how to get data between just startdate and enddate, but I need to select it between just month and year, start month is previous July and current year,  so for example, July 2019(startdate) and April 2020(enddate). I am having issues with the between, not sure how to format it. can anyone provide assistance? start date will always be the month of July. The user will select a @p_startmonth,@p_startyear and @p_endmonth,@p_endyear. This is used in a query for ssrs dataset.

  • Use DATEFROMPARTS to build the date from the given year and month.  For the end date, you'll want to add 1 to the month so that you include the whole month.  Use WHERE date >= startdate AND date < enddate instead of BETWEEN.

    John

  • Like this?

    WHERE site_code = @p_site_code

    AND datepart(month,observ_date) = @p_startmonth

    AND DATEPART(YEAR,observ_date)= @p_startyear

    AND DATEPART(MONTH,observ_date) <= @p_endmonth

    AND DATEPART(YEAR,observ_date)= @p_endyear

  • If you tried that on your data, you'd see that it won;t work.  It'll only select dates in the start month because you use "=" instead of ">".  Also, it won't perform well if you have a large data set since (a) the DATEPART calculations have to be done on every individual row and (b) they make the query non-sargable, meaning that any index on the column won't be able to be used for seeks.  As I suggested, use DATEFROMPARTS to build the start date and the end date.  Then use DATEADD to add one month to the end date so that you capture everything up to the end of the end month instead of the beginning of it.

    John

  • Short example:

    DECLARE
    @m VARCHAR(10) = 'July', @y INT = 2019, @mnum TINYINT;
    SELECT @mnum = CASE
    WHEN @m = 'January' THEN
    1
    WHEN @m = 'February' THEN
    2
    WHEN @m = 'March' THEN
    3
    WHEN @m = 'April' THEN
    4
    WHEN @m = 'May' THEN
    5
    WHEN @m = 'June' THEN
    6
    WHEN @m = 'July' THEN
    7
    WHEN @m = 'August' THEN
    8
    WHEN @m = 'September' THEN
    9
    WHEN @m = 'October' THEN
    10
    WHEN @m = 'November' THEN
    11
    WHEN @m = 'December' THEN
    12
    END;
    SELECT DATEFROMPARTS(@y, @mnum, 1);

    If you don't have standard names, you might need more complex mapping for the months.

  • Honestly, just give them a datepicker and let them pick it that way. Users need to have some small responsibility here.

    -------------------------------------------------------------------------------------------------------------------------------------
    Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

  • You know what today is, using GETDATE().  You can use EOMONTH() to get the last day of the current month.  Then use DATEADD to add one day to that.  Save as a variable; this is the max date you want.  E.g. @MaxDate

    Use DATEFROMPARTS to get the starting date.

    Is is always July of the previous year? Then you can use DATEPART to the the current year, subtract one, and use that year in DATEFROMPARTS, along with month 7 and day 1.

    Or is it the previous July?  For example, if the current date was Oct 2, 2020, you'd want July 1, 2020.  That makes it a little more difficult.  You'd also have to get the month from DATEPART, and if <= 6 you would subtract 1 from the current year as above.  If the current month was >= 7, you'd just use the current year.

    Either way, when you get the desired July 1 you want, store it in a variable, say @MinDate.

    Then in your WHERE clause, you'd say

    WHERE observ_date >= @MinDate  -- greater or equal to the July 1 date
    AND observ_date < @MaxDate -- strictly less than the first day of the month

    If the users will select the dates - they are not always looking for July 1 through the current month - you'd use one of the other solutions above.

    • This reply was modified 3 years, 10 months ago by  GaryV. Reason: Clarified comment in code block
  • You might consider using an expression like this, which would work with BETWEEN

    100 * YEAR(observ_date) + MONTH(observ_date)

    e.g. today's value would be 202005

     

  • WILLIAM MITCHELL wrote:

    You might consider using an expression like this, which would work with BETWEEN

    100 * YEAR(observ_date) + MONTH(observ_date)

    e.g. today's value would be 202005

    That would make for a non-SARGable WHERE clause.

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

  • cbrammer1219 wrote:

    I know how to get data between just startdate and enddate, but I need to select it between just month and year, start month is previous July and current year,  so for example, July 2019(startdate) and April 2020(enddate). I am having issues with the between, not sure how to format it. can anyone provide assistance? start date will always be the month of July. The user will select a @p_startmonth,@p_startyear and @p_endmonth,@p_endyear. This is used in a query for ssrs dataset.

    So, if today were 15 Sep 2020 (for example), what date would you want to start on?  1 July 2020 or 1 July 2019?  If the latter, what is the logic of your selection?

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

  • cbrammer1219 wrote:

    Like this?

    WHERE site_code = @p_site_code

    AND datepart(month,observ_date) = @p_startmonth

    AND DATEPART(YEAR,observ_date)= @p_startyear

    AND DATEPART(MONTH,observ_date) <= @p_endmonth

    AND DATEPART(YEAR,observ_date)= @p_endyear

    Not SARGable.  Answer the question I just posted and let's put this problem to bed.

     

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

  • cbrammer1219 wrote:

    ... start date will always be the month of July. The user will select a @p_startmonth,@p_startyear and @p_endmonth,@p_endyear. ...

    I just noticed this.  If the start date will always be the month of July, why would a user be selecting a start month and start year?

  • GaryV wrote:

    cbrammer1219 wrote:

    ... start date will always be the month of July. The user will select a @p_startmonth,@p_startyear and @p_endmonth,@p_endyear. ...

    I just noticed this.  If the start date will always be the month of July, why would a user be selecting a start month and start year?

    My question, as well.... along with my other question I asked just a bit ago.

    --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 wrote:

    cbrammer1219 wrote:

    I know how to get data between just startdate and enddate, but I need to select it between just month and year, start month is previous July and current year,  so for example, July 2019(startdate) and April 2020(enddate). I am having issues with the between, not sure how to format it. can anyone provide assistance? start date will always be the month of July. The user will select a @p_startmonth,@p_startyear and @p_endmonth,@p_endyear. This is used in a query for ssrs dataset.

    So, if today were 15 Sep 2020 (for example), what date would you want to start on?  1 July 2020 or 1 July 2019?  If the latter, what is the logic of your selection?

    Heh... so... any interest in your own problem that you posted. 😉

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

Viewing 14 posts - 1 through 13 (of 13 total)

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