Is there a way to SELECT by a part of a date in SQL?

Question

I have a datetime column in a MySQL table and I'd like to select records that have the same year, month, and day (but perhaps different hour and minutes). In other cases, I'd select ones with the same year and month. Is there a way to do that?

Answer 1

I've seen a date conversion done this way before in SQL server (not sure if this works in MySQL):

select convert(varchar(10), getdate(), 120)

(This is just an example of how to convert the date into just YYYY-MM-DD)

Answer 2

There are functions like year() and hour() that will do this for you.

 select * from users where year(lastlogin) = year(now())

Answer 3

You can use some of the MySQL Date and Time Functions to extract/compare the parts of the date you're interested in.

SELECT * FROM table WHERE DATE(datetime1) = DATE(datetime2);

SELECT * FROM table WHERE YEAR(datetime1) = YEAR(datetime2) AND MONTH(datetime1) = MONTH(datetime2)

etc

Answer 4

SELECT
  foo
FROM
  FooTable
WHERE
  DATEADD(dd, 0, DATEDIFF(dd, 0, TheDate)) = DATEADD(dd, 0, DATEDIFF(dd, 0, @SomeDate))

This: DATEADD(dd, 0, DATEDIFF(dd, 0, TheDate))

• calculates the date (in days) difference from date 0
• adds the result (in days) to date 0

effectively setting the time part of a DATETIME value to 00:00:00, without the need to do string conversion or such.

Answer 5

The fastest way to do this is using BETWEEN, for example:

SELECT fields FROM table WHERE
datecolumn BETWEEN '2001-01-01 00:00:00' AND '2001-12-31 23:59:59';

Answer 6

I know I'm leaving off the time, but the concept is the same... assume datetime is like: 12/25/2009

SELECT * FROM table
WHERE SUBSTRING(datetime, 6, 4) = targetYear
AND SUBSTRING(datetime, 0, 2) = targetMonth

Answer 7

Yes, you want to use one of the following date functions in your selection

Answer 8

You can also do

SELECT * FROM table WHERE datetime LIKE '2008-08-01%'

or

SELECT * FROM table WHERE datetime LIKE '2007-05%'