SQL Select Records based on current date minus two days

sql sql-server select getdate sql-date-functions
Emma picture Emma · Dec 14, 2017 · Viewed 32.9k times · Source

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?

Answer

DhruvJoshi picture DhruvJoshi · Dec 14, 2017

you should try to use dateadd function

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

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

Related questions

How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
SQL update from one Table to another based on a ID match

I have a database with account numbers and card numbers. I match these to a file to update any card numbers to the account number, so that I am only working with account numbers. I created a view linking the …

Read More
Best way to do nested case statement logic in SQL Server

I'm writing an SQL Query, where a few of the columns returned need to be calculated depending on quite a lot of conditions. I'm currently using nested case statements, but its getting messy. Is there a better (more organised and/…

Read More