Selecting data between just month and year

Question

Selecting data between just month and year

cbrammer1219

SSCarpal Tunnel

Points: 4476
More actions
April 29, 2020 at 3:53 pm

#3747362

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.

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

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

cbrammer1219 SSCarpal Tunnel Points: 4476 More actions · Answer 2

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

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

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 4

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.

jonathan.crawford SSCertifiable Points: 6613 More actions · Answer 5

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

GaryV Hall of Fame Points: 3307 More actions · Answer 6

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 4 years, 7 months ago by GaryV. Reason: Clarified comment in code block

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 7

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

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)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

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)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 10

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)

GaryV Hall of Fame Points: 3307 More actions · Answer 11

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?

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 12

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 SSC Guru Points: 1004406 More actions · Answer 13

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)