7

I have an orders table which contains an order ID, order date and order description.

I want to run a select query which captures all orders that have been created in the last two days. so the current date minus two days. from the 14th December, I would want to select all orders where the order date is > 13th December. This needs to use a Get date function to pick up the current date and minus the days.

I have tried:

select * from orders where orderdate > getdate() - 2

but this is not producing the correct results. Any idea's how to do this please?

Emma
  • 103
  • 1
  • 1
  • 6
  • use convert( varchar(10), orderdate,120) = convert( varchar(10), getdate()-2,120) in where clause – Ven Dec 14 '17 at 16:42
  • Possible duplicate of [SQL statement to select all rows from previous day](https://stackoverflow.com/questions/1503298/sql-statement-to-select-all-rows-from-previous-day) – Tab Alleman Dec 14 '17 at 16:54
  • 3
    You contradict yourself: "created prior to the last two days" vs "from the 14th December, I would want to select all orders where the order date is > 13th December". If the date is GREATER than 13th, that's NOT "prior to". – Tab Alleman Dec 14 '17 at 16:56

4 Answers4

14

you should try to use dateadd function

select * from orders where orderdate > dateadd(dd,-1,cast(getdate() as date))

Now, this may be exactly what you need but then you need to understand that by casting to date we remove the time part and effectively go back to the start of the day and a day behind it(-1) gives the start of yesterday.

Azeez Ibrahim
  • 13
  • 5
DhruvJoshi
  • 17,041
  • 6
  • 41
  • 60
  • That has worked, thank you and thank you for the explanation, this will help in the future! – Emma Dec 14 '17 at 17:01
3

Try this:

select * from orders where orderdate > cast(getdate() - 1 as date)
Niranjan Rajawat
  • 553
  • 2
  • 9
  • 1
    For years I used dateadd(day .. , for no reason. Thank you! `select * from orders where orderdate > getdate() - 1` works as well, without the cast!! – AJ AJ Dec 20 '20 at 08:16
3

If you want the orders of the last two days, use DATEADD to add days to today's date (in your case -2 days) then use DATEDIFF to compare the two days:

SELECT * FROM orders
WHERE DATEDIFF(DAY, DATEADD(DAY, -2, GETDATE()), orderdate) > 0

Now, assuming all orders have dates in the past and none in the future (which is what it should be), you can simply use DATEDIFF like this:

SELECT * FROM orders
WHERE DATEDIFF(DAY, orderdate, GETDATE()) <= 2

Note: you can use < 3 instead of <= 2.

Gemini Girl
  • 39
  • 1
  • 10
Racil Hilan
  • 24,690
  • 13
  • 50
  • 55
1

Looks like dateadd and convert will solve the problem. 

select   o.*
from     orders o
where    o.orderdate >= dateadd(day, -2, convert(date, getdate()))
StelioK
  • 1,771
  • 1
  • 11
  • 21