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

Using SQL, I want to return all records where the date is between 1st March and 31st June (for example), but the records should cover all years. Is there a simple way开发者_开发问答 I can achieve this?

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

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

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

For SQL Server use:

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

Use the date functions to get the Month and the Day of Month from the date field and use in the where clause.

Depending on your DB, the function names may vary. But it will be in general 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)

Try this, definitely work

SELECT * FROM Table WHERE Month(DateColumn) IN (3, 4, 5, 6)

For SQL Server I'll use following. eg:between 1st March and 31st June

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 it helps..:)