filter results based on smalldatetime comparison

985 Views Asked by At

i have my database date stored in smalldatetime. now, if i need to filter the results based on(say today's date equals the date in database), what is the best way to do it?

select * from tbl Emp where Emp.date = cast(getdate() as smalldatetime) 

//this will not match the date

the other way i found but not sure if casting the date in table affects performance like:

select * from tbl Emp where cast(Emp.date as date) = cast(getdate() as date)
1

There are 1 best solutions below

3
On BEST ANSWER

This method is accurate:

where cast(Emp.date as date) = cast(getdate() as date)

This method is accurate and can use an index:

where (Emp.date >= cast(getdate() as date) and
       Emp.date < cast(getdate() + 1 as date))

The + 1 is a convenience in SQL Server that adds one day to a datetime value. But, you can also use

where (Emp.date >= cast(getdate() as date) and
       Emp.date < dateadd(day, 1, cast(getdate() as date))
      )