SQL DateTime Operations

Published on Wednesday, March 9, 2011
sql

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 <a href="http://msdn.microsoft.com/en-us/library/ms187928.aspx">here</a>)


SELECT * FROM DATE_SAMPLE 
WHERE SAMPLE_DATE &gt;= '2003-04-09' 
AND SAMPLE_DATE &lt;'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.