How to cast datetime into date in SQL Server 2005

Question

I have used something like this:

 select* from table where cast(CreatedDate as date) = cast(GETDATE() as DATE)

in SQL Server 2008R2 and it works perfectly. But when goes to 2005, it doesn't work because Date is not a defined system type. Can anyone let me know a way that works both 2005 and 2008R2?

The CreatedDate is default as current_timestamp and I want to get data with today's data only.

As you say in the question, `DATE` data type doesn't exists in SQL Server 2005, so you can't cast a `datetime` into `date`. You can, however, just remove the time part of the `datetime` instead — Lamak, Oct 21 '15 at 14:22
surely you've googled this question? http://stackoverflow.com/questions/113045/how-to-return-the-date-part-only-from-a-sql-server-datetime-datatype — devlin carnate, Oct 21 '15 at 14:27
One thing to note is you should avoid calling the functions on your actual data, and just on the parameter, so you would end up with ` WHERE CreatedDate >= DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE())) AND CreatedDate < DATEADD(DAY, 1, DATEDIFF(DAY, 0, GETDATE()))` --- i.e. greater than or equal to midnight today, but less than midnight tomorrow. This ensures your query remains [sargable](https://en.wikipedia.org/wiki/Sargable) and can make proper use of indexes and statistics. — GarethD, Oct 21 '15 at 14:46

score 0 · Answer 1 · answered Oct 21 '15 at 14:31

You can try this:

convert(DateTime, floor(convert(float, getdate())))

That gets rid of the time part, by converting it into the underlying float number and rounding down to the nearest integer. The integer part represents the date and the decimal part is the time, so converting the integer back to a DateTime give the same date you started with but without any time part.

You can also wrap it up as a scalar-valued function:

CREATE FUNCTION [dbo].[DateTrunc] (@date DateTime)
RETURNS DateTime
AS
BEGIN
    return convert(DateTime, floor(convert(float, @date)))
END

How to cast datetime into date in SQL Server 2005

1 Answers1