CODEWITHSUNDEEP
sqlsql-servert-sql
user42348
user42348

Reputation: 4319

To get date from datetime in 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?

Upvotes: 1

Views: 1050

Answers (5)

Matthew Perron
Matthew Perron

Reputation: 6291

The simplest solution might be :

SELECT CAST(GETDATE() as DATE)

Upvotes: 2

Peter Di Cecco
Peter Di Cecco

Reputation: 1578

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())

Upvotes: 2

OMG Ponies
OMG Ponies

Reputation: 332501

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.

Upvotes: 3

Carlos
Carlos

Reputation: 2513

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.

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166326

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.

Upvotes: 3

Related Questions