SQL for in between dates for multiple years

user_17462612 0
Viewed 25

I'm doing free courses in the Oracle devgym and I came across a question about dates that I am unfamiliar with. If I have a table of sales with dates and I want to display only dates between any two given months for every possible year within the sales table how would I go about doing that. This is what I have but I don't know how to do it for multiple years without is showing everything in between those years also. I'm trying for instance to show all sales for dates in between August and December for every year in the table. Is it possible to do without knowing all the years within the table up front?

Select *
From Sale
Where SaleDate BETWEEN '08/01/2013' and '12/31/2013'
Order by SaleDate
datatables syntax date sql database
2 Answers

You can use EXTRACT(MONTH FROM date) to get the month of a date

Select *
From Sale
Where EXTRACT(MONTH FROM SaleDate) BETWEEN 8 and 12
Order by SaleDate
user_51935360

The best way to tackle this is to use the DAYOFYEAR() function in MYSQL which makes things very easy. It returns a value from 1 to 366 for the day of the year. This totally eliminates the year restriction ALSO maintaining the bound of a single year. If you need to search between years, you have to specify the year numbers. Select * From Sale Where SaleDate BETWEEN DAYOFYEAR(08/01/2013) and DAYOFYEAR(12/31/2013) Order by SaleDate;

user_193783700
Related