Time comparison in SQL

Time on the form 2009-12-31 22:12:34 can be hard to compare exactly. Therefore it is useful to have some ways of simplifying this.

Using DATE_FORMAT is useful.
SELECT * FROM table WHERE DATE_FORMAT(column, '%Y%m%d') = '20091231'
or
SELECT * FROM table WHERE DATE_FORMAT(column, '%Y-%m-%d') = '2009-12-31'
will give you rows with column value of that day.

The following can also be used apparantly:
WHERE DateDiff(dd, OrderDate, '01/01/2006') = 0
WHERE Convert(varchar(20), OrderDate, 101) = '01/01/2006'
WHERE Year(OrderDate) = 2006 AND Month(OrderDate) = 1 and Day(OrderDate)=1
WHERE OrderDate LIKE '01/01/2006%'
WHERE OrderDate >= '01/01/2006' AND OrderDate < '01/02/2006'

and

WHERE OrderDate BETWEEN '01/01/2006' AND '01/02/2006'

In addition, getting values where date is less than or larger than a given difference from now or a given time. On can use INTERVAL:
SELECT * FROM table WHERE createdate < DATE_SUB(NOW(),INTERVAL 3 DAY)
will give rows where createdate is before 3 days ago.

Solutions found at http://www.w3schools.com/SQL/func_date_format.asp and http://demiliani.com/blog/archive/2006/01/19/3384.aspx