SQL – How to Get Date from Datetime in SQL Server

sqlsql-servert-sql

I have datecreated field in a table. It contains value as "2009-12-30 11:47:20:297"
I have a query like this:

select * 
  from table 
 where DateCreated = getdate()

Although one row exists with today's date, I am not getting that row while executing above query. Can anybody help?

Best Answer

The reason why your query doesn't return the row you expect, is because GETDATE() returns the date and time portion at the moment the query was executed. The value in your DateCreated column will not match the time portion, so no rows are returned.

There are various ways to construct a query so that it evaluates the date based on only the date component. Here's one example:

WHERE YEAR(datecreated) = YEAR(GETDATE())
  AND MONTH(datecreated) = MONTH(GETDATE())
  AND DAY(datecreated) = DAY(GETDATE())

The unfortunate reality is that any query using a function on the column means that if an index exists on the column, it can't be used.

Related Question