Geek Answers Handbook

In SQL, how to return records matching date and month (ignore year)

Using SQL, I want to return all records where the date is between March 1 and June 31 (for example), but the records should cover all years. Is there an easy way to achieve this?

+4
source share
5 answers

Here is what you could do if using PL / SQL or oracle SQL +

SELECT * FROM table WHERE TO_CHAR(MONTH_COLUMN,'MM/DD') = '06/21'

this will give you all rows that have a date of June 21, regardless of the year.

+1
source

Use the date functions to get the month and day of the month from the date field and use in the where clause.

Function names may vary depending on your database. But it will be like

 SELECT * FROM table WHERE Month(dateField) = 6 AND (DayOfMonth(dateField) >= 1 AND DayOfMonth(dateField) <= 30)

in SQL Server:

 SELECT * FROM table WHERE Month(dateField) = 6 AND (Day(dateField) >= 1 AND Day(dateField) <= 30)
0
source

To use SQL Server:

 select * from table where month(dtgCol) between 3 and 6
0
source

Try it, definitely work

SELECT * From table WHERE Month (DateColumn) IN (3, 4, 5, 6)

0
source

For SQL Server, I will use the following. for example: from March 1 to June 31

 select * from ( select *,DATEFROMPARTS(2011,MONTH(CreateDate),DAY(CreateDate)) as dt from tblAction ) as x where x.dt between DATEFROMPARTS(2011,3,1) and DATEFROMPARTS(2011,6,31)

See if that helps. :)

0
source

More articles:


All Articles