Comparing results with today's date?

Question

Is there a way to use the Now() function in SQL to select values with today's date?

I was under the impression Now() would contain the time as well as date, but today's date would have the time set to 00:00:00 and therefore this would never match?

Answer 1

Building on the previous answers, please note an important point, you also need to manipulate your table column to ensure it does not contain the time fragment of the datetime datatype.

Below is a small sample script demonstrating the above:

select getdate()
--2012-05-01 12:06:51.413
select cast(getdate() as date)
--2012-05-01

--we're using sysobjects for the example
create table test (id int)
select * from sysobjects where cast(crdate as date) = cast(getdate() as date)
--resultset contains only objects created today
drop table test

I hope this helps.

EDIT:
Following @dwurf comment (thanks) about the effect the above example may have on performance, I would like to suggest the following instead. We create a date range between today at midnight (start of day) and the last millisecond of the day (SQL server count up to .997, that's why I'm reducing 3 milliseconds). In this manner we avoid manipulating the left side and avoid the performance impact.

select getdate()
--2012-05-01 12:06:51.413
select dateadd(millisecond, -3, cast(cast(getdate()+1 as date) as datetime))
--2012-05-01 23:59:59.997
select cast(getdate() as date)
--2012-05-01

create table test (id int)
select * from sysobjects where crdate between cast(getdate() as date) and dateadd(millisecond, -3, cast(cast(getdate()+1 as date) as datetime))
--resultset contains only objects created today
drop table test

Answer 2

where created_date between CURRENT_TIMESTAMP-180 and CURRENT_TIMESTAMP

Answer 3

There is no native Now() function in SQL Server so you should use:

select GETDATE() --2012-05-01 10:14:13.403

you can get day, month and year separately by doing:

select DAY(getdate())  --1
select month(getdate())  --5
select year(getdate()) --2012

if you are on sql server 2008, there is the DATE date time which has only the date part, not the time:

select cast (GETDATE() as DATE) --2012-05-01