To get date from datetime in sql

Question

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?

Answer 1

You can use something like this with Sql Server

CREATE FUNCTION [dbo].[udf_DateOnly](@DateTime DATETIME)
RETURNS DATETIME
AS
BEGIN
    RETURN DATEADD(dd,0, DATEDIFF(dd,0,@DateTime))
END

This line

DATEADD(dd,0, DATEDIFF(dd,0,@DateTime))

will strip out the Date portion.

Answer 2

You can convert datetime to a string with only the date by using
CONVERT(varchar(8), GETDATE(), 112)

If needed, you can then change it back to datetime and as a result you'll get a datetime with the hours, minutes, seconds and milliseconds set to zero.

Answer 3

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.

Answer 4

The datetime field includes both the date and the time, accurate to the millisecond. Your query will only work if it is the exact millisecond stored in the database.

To check if it is today, but ignore the time of day, you can check for a range like this:

select * from table where
DateCreated >= '2009-12-30' and
DateCreated < '2009-12-31'

You can use that in conjunction with a function that converts the current date, as astander or Khilon has posted. Here is a full example using astander's answer. Also, as Craig Young points out, this will work with indexes.

select * from table where
DateCreated >= DATEDIFF(dd,0,GETDATE()) and
DateCreated < DATEDIFF(dd,0,GETDATE())