views - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

views

Author

Topic

lisa_baria03
Yak Posting Veteran

60 Posts

Posted - 2007-11-17 : 10:56:56

I've created views ProductsSold 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 just wonder if there is a more simpler way to query it?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-17 : 11:03:53

Where datediff(hh,orderdate,getdate()) <= 24

Will calculate the difference in orders between getdate() and the order date. Filtering for <= 24 will be hours

Where datediff(d,orderdate,getdate()) = 1

Will return the records where the orderdate is 1 day prior to the getdate()

Poor planning on your part does not constitute an emergency on my part.

lisa_baria03
Yak Posting Veteran

60 Posts

Posted - 2007-11-17 : 11:15:42

order date in last twenty four hours between getdate()-1 and getdate()

do these both return the same for the above?

Where datediff(d,orderdate,getdate()) = 1
Where datediff(hh,orderdate,getdate()) <= 24

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-17 : 12:17:25

Have you tried them to see if there are different results?

Basically, they are doing the same thing.

Poor planning on your part does not constitute an emergency on my part.

lisa_baria03
Yak Posting Veteran

60 Posts

Posted - 2007-11-18 : 17:26:13

can you tell me the difference between datediff and dateadd and which method would be more easier to retrieve data in the twenty four hours between getdate()-1 and getdate().

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-18 : 17:46:54

it is a matter of preference. getdate()-1 is 1 day prior. dateadd(-1,..) is 1 day prior.

My suggestion would be to actually try things, and see which one returns the results you need.

Poor planning on your part does not constitute an emergency on my part.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-18 : 20:27:03

>> can you tell me the difference between datediff and dateadd

Books online tells you.

lisa_baria03
Yak Posting Veteran

60 Posts

Posted - 2007-11-19 : 18:58:20

Where datediff(d,orderdate,getdate()) = 1
Where datediff(hh,orderdate,getdate()) <= 24

i tested the two queries using these 2 different "where clauses"
both produced different results.

would you be able to let me know which would list the rows from the table that displays products that were ordered in the previous 24 hours between getdate()-1 and getdate()

thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-11-19 : 19:49:55

What is you WHERE clause like?

These are the same:
SELECT GETDATE() - 1, DATEADD(HOUR, -24, GETDATE())

Your WHERE clause should look something like: WHERE OrderDate > (GETDATE() - 1) AND OrderDate <= GETDATE()

lisa_baria03
Yak Posting Veteran

60 Posts

Posted - 2007-11-19 : 19:55:24

your solution worked out, but i thought this would also do the same?

Where datediff(d,orderdate,getdate()) = 1
Where datediff(hh,orderdate,getdate()) <= 24

but it produce more results.

Butterfly82
Starting Member

30 Posts

Posted - 2007-11-19 : 21:29:12

Hi Lisa,

This link may help when it comes to explaining the difference between DATEADD and DATEDIFF:

http://msdn2.microsoft.com/en-us/library/ms187081.aspx

It seems to me (without testing them) that using:

datediff(d,orderdate,getdate()) = 1

and

datediff(hh,orderdate,getdate()) <= 24

in the WHERE clause may produce different outcomes (depending on the data in your tables), this is due to the first example only picking up rows when a full 24 hour period has elapsed exactly and the second example picking up anything that is within 24 hours.

Hope this helps

Butterfly82

Butterfly82
Starting Member

30 Posts

Posted - 2007-11-19 : 22:04:04

Ok, I just tested the two different versions of implementing DATEDIFF mentioned above and as I thought the results came out different for the reason I explained in the previous post.

However, you can cause them to produce the same output by simply applying the less than (<) operator to the equals (=) in the first example, like this:

DATEDIFF(d,orderdate,GETDATE()) <= 1

This will now produce the same output as the second example, because less than or equal to one day is the same as less than or equal to 24 hours.

Butterfly82

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-19 : 22:05:48

Nobody quoted this article ? http://www.sqlteam.com/article/datediff-function-demystified

KH
[spoiler]Time is always against us[/spoiler]

Butterfly82
Starting Member

30 Posts

Posted - 2007-11-19 : 22:35:38

quote:
Nobody quoted this article ? http://www.sqlteam.com/article/datediff-function-demystified

Very good article, I learnt from reading it.

Thanks khtan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-19 : 22:38:11

I am just a messenger. Should also give credit to the author

KH
[spoiler]Time is always against us[/spoiler]

Butterfly82
Starting Member

30 Posts

Posted - 2007-11-19 : 22:44:25

quote:
Originally posted by khtan

I am just a messenger. Should also give credit to the author

Your not wrong, so if you're out there Peter Larsson, thank you!
Oh and khtan thank you for leading me to it

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-11-20 : 11:47:53

I'm not sure if you were just going thorugh the paces on this to learn about DATEDIFF and DATEADD, but it should be noted that if you do this: "DATEDIFF(d,orderdate,GETDATE()) <= 1" SQL will not be able to use an index becuase you are applying a function to a column. Better to use: WHERE OrderDate > (GETDATE() - 1) AND OrderDate <= GETDATE() or something similar for performance (assuming there is an index to be sued on OrderDate).

lisa_baria03
Yak Posting Veteran

60 Posts

Posted - 2007-11-20 : 21:57:12

thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources