CODEWITHSUNDEEP
sqlmysqldate
user29772
user29772

Reputation: 1467

SQL date selecting

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?

Upvotes: 3

Views: 302

Answers (6)

Guffa
Guffa

Reputation: 700152

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);

Upvotes: 3

TheTXI
TheTXI

Reputation: 37875

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

Upvotes: 6

Quassnoi
Quassnoi

Reputation: 425251

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.

Upvotes: 2

HLGEM
HLGEM

Reputation: 96542

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

Upvotes: 1

ChrisLively
ChrisLively

Reputation: 88044

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

Upvotes: 3

sfossen
sfossen

Reputation: 4778

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

Upvotes: 0

Related Questions