SQL DateTime Operations

SQL DateTime Operations:

SELECT * FROM DATE_SAMPLE WHERE SAMPLE_DATE = ’2003-04-09′

SELECT * FROM DATE_SAMPLE WHERE SAMPLE_DATE between ’2003-04-09′
AND ’2003-04-10′

SELECT * FROM DATE_SAMPLE
WHERE CONVERT(CHAR(10),SAMPLE_DATE,120) = ’2003-04-09′
(about why use 120 above, please check MSDN here)

SELECT * FROM DATE_SAMPLE
WHERE SAMPLE_DATE >= ’2003-04-09′
AND SAMPLE_DATE <’2003-04-10′

SELECT * FROM DATE_SAMPLE
WHERE
DATEPART(YEAR, SAMPLE_DATE) = ’2003′ AND
DATEPART(MONTH,SAMPLE_DATE) = ’04′ AND
DATEPART(DAY, SAMPLE_DATE) = ’09′

SELECT * FROM DATE_SAMPLE WHERE
CAST(FLOOR(CAST(SAMPLE_DATE AS FLOAT))AS DATETIME) =
’2003-04-09′

…

Above samples from databasejournal, they have very detail explanation for those samples. Please read there.