April 29, 2020 at 3:53 pm
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.
April 29, 2020 at 4:00 pm
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
April 29, 2020 at 4:06 pm
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
April 30, 2020 at 9:27 am
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
April 30, 2020 at 3:40 pm
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.
April 30, 2020 at 4:58 pm
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
May 5, 2020 at 12:07 pm
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.
May 5, 2020 at 1:50 pm
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
May 5, 2020 at 3:10 pm
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
Change is inevitable... Change for the better is not.
May 5, 2020 at 3:31 pm
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
Change is inevitable... Change for the better is not.
May 5, 2020 at 3:32 pm
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
Change is inevitable... Change for the better is not.
May 5, 2020 at 3:38 pm
... 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?
May 5, 2020 at 4:31 pm
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
Change is inevitable... Change for the better is not.
May 9, 2020 at 10:31 pm
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
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply