SQL for in between dates for multiple years-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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;