SQL date selecting

Question

I want to be able to select all database rows where the month and year are the same as what I am searching for. Since the DATE field has year, month, and day, how do I search with year and month?

Answer 1

SELECT * 
FROM tblTableName 
WHERE Month(ColumnDate) = Month(MyDate) 
    AND Year(ColumnDate) = Year(MyDate)

Answer 2

You will want to use MONTH() and YEAR() in mysql.

Answer 3

SELECT *
FROM myTable
WHERE ( YEAR(myfield) = '2009')
  AND ( MONTH(myfield) = '1')

Answer 4

That would depend on what database backend you are using. IN SQl Server I would use

where year(datefield) = @year and month (datefield) - @month

to do this.

or you could build a where clause by creating a date range

where datefield between 20090101 and 20090201

Answer 5

In MySQL:

SELECT *
FROM   mytable
WHERE  date >= STR_TO_DATE(CONCAT(EXTRACT(YEAR_MONTH FROM @mydate), '01'), '%Y%m%d')
   AND date < STR_TO_DATE(CONCAT(EXTRACT(YEAR_MONTH FROM @mydate), '01'), '%Y%m%d') + INTERVAL 1 MONTH

This will efficiently use an index on the date field.

Answer 6

The most efficient is to create the start and end date of the range that you want, so that you compare the dates as a single value instead of extracting the year and month properties from each date.

Example:

select SomeField
from SomeTable
where SomeDate >= ? and SomeDate < ?

(Note that the first comparison is inclusive and the seond is exclusive.)

Create the start and end date to use as parameters: (example in C#)

DateTime start = new DateTime(date.Year, date.Month, 1)
DateTIme end = start.AddMonths(1);