0

I am trying to get all the records from the last 2 days but excluding today's date. I want to get name and location where date submitted is included for the last 2 days only. The data type for date_submitted is datetime.

select name, location 
from myTable 
where date_submitted in (select CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME))
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
moe
  • 5,149
  • 38
  • 130
  • 197
  • 1
    Why cast date as float? – Zane Feb 24 '15 at 16:34
  • @Zane: It seems the best performing way to truncate a date: http://stackoverflow.com/a/923322/993547. – Patrick Hofman Feb 25 '15 at 07:51
  • Your reference appears to indicate that `Cast( GetDate() as Date )` is preferred as it is documented to work correctly and doesn't depend on the current binary format for `DATETIME`. It's also clearer as to your intent and clarity has considerable value in the real world. – HABO Feb 25 '15 at 15:17

1 Answers1

9

Don't use in, use >=. You can also use dateadd:

where date_submitted >= cast(dateadd(day, -2, getdate()) as date)
and   date_submitted < cast(getdate() as date)
Patrick Hofman
  • 153,850
  • 22
  • 249
  • 325