Monday, September 28, 2009

Get Date and Time from the DateTime in SQL Server

Have you wanted to create a where clause the brings back all records for a given date? More specifically, today's date?

SELECT * FROM [database].[dbo].[myTable] Where Convert(varchar(8),CreatedDate,112) = Convert(varchar(8),getdate(),112)

For a date range you can use the between key word:

SELECT * FROM myTable WHERE createdate BETWEEN '2001-01-01' AND '2021-12-31'

or records for the last 5 days:

SELECT * FROM [database].[dbo].[myTable] Where CreatedDate BETWEEN getdate()-5 AND getdate()

To extract the time from a datetime value you can do the following:

declare @time datetime
set @time=CONVERT(varchar(8),
GETDATE(), 108)

Thanks to Dankov for the time and date extraction code.

No comments: