• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1156
  • Last Modified:

getdate() question

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
salesprod
Asked:
salesprod
  • 2
3 Solutions
 
imitchieCommented:
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
 
salesprodAuthor Commented:
no wonder there were more results displayed. That helps! thanks!
so just to confirm it will return results in the last 24 hours?
0
 
moondistCommented:
I think this will work for you..

select orderdetails.ProductID, orderdetails.OrderQty, orders.OrderDate
from OrderDetails inner join Orders
on OrderDetails.salesOrderID = Orders.salesOrderID
where OrderDate > DATEADD(hh,-24,getdate())

0
 
imitchieCommented:
yes, between getdate()-1 and getdate() will give you 24 hours
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now