Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# getdate() question

Posted on 2007-11-19
Medium Priority
1,151 Views
I've created a views that will list all the products- ProductID and quantity, that were ordered (OrderDate) in last twenty four hours between getdate()-1 and getdate().

create view ProductsSold
as
select orderdetails.ProductID, orderdetails.OrderQty, orders.OrderDate
from OrderDetails inner join Orders
on OrderDetails.salesOrderID = Orders.salesOrderID
where OrderDate > dateadd(d, -1, getdate()) and OrderDate < getdate()

I also tried these:
Where datediff(hh,orderdate,getdate()) <= 24
Where datediff(d,orderdate,getdate()) = 1

but they all produced different results. I thought they would produced the same results.
is it possible to say: where OrderDate between getdate()-1 and getdate()
0
Question by:salesprod
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 25

Accepted Solution

imitchie earned 1600 total points
ID: 20316823
yes you can just use

where OrderDate between getdate()-1 and getdate()

the differences:
1.Where datediff(hh,orderdate,getdate()) <= 24
a.datediff HH returns only the difference between the hours, and ignores minutes. so you get some more orders creeping into the result set

2.Where datediff(d,orderdate,getdate()) = 1
a.same as above. datediff(d, '2007-01-01', '2007-01-02 23:59:59') is 1
0

Author Comment

ID: 20316842
no wonder there were more results displayed. That helps! thanks!
so just to confirm it will return results in the last 24 hours?
0

LVL 4

Assisted Solution

moondist earned 400 total points
ID: 20316856
I think this will work for you..

select orderdetails.ProductID, orderdetails.OrderQty, orders.OrderDate
from OrderDetails inner join Orders
on OrderDetails.salesOrderID = Orders.salesOrderID

0

LVL 25

Assisted Solution

imitchie earned 1600 total points
ID: 20316870
yes, between getdate()-1 and getdate() will give you 24 hours
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
###### Suggested Courses
Course of the Month11 days, 23 hours left to enroll