| Author |
Topic |
|
themoney32
Starting Member
20 Posts |
 Posted - 2009-09-21 : 01:56:35
|
| I am trying to find a query that will show the number of days between two dates. For example if I have a orderDate and a shippedDate field. And I want to show orders that were shipped more than a week later? I also need to show how many days late they are? I also need to show the date in the format like this "Jan 21, 1997". And to top it all off I need to eliminate the fields where the order was not shipped, as in the shippedDate field was null. Are there any suggestions? |
|
|
themoney32
Starting Member
20 Posts |
Posted - 2009-09-21 : 02:17:28
|
| I have found out to use DATEDIFF to find the difference between the two dates, however would anyone know how to only show the fields that are greater than 7? For example the following DATEDIFF(ShippedDate, OrderDate) > 7 Of course that doesnt work but I need to find something that would only display it greater than 7 |
 |
|
|
senthil_nagore
Master Smack Fu Yak Hacker
1007 Posts |
Posted - 2009-09-21 : 02:22:35
|
| Try Like thisselect <column1>,case when DATEDIFF(d,ShippedDate, OrderDate) > 7 then <column_name> end from<table_name>Senthil.C------------------------------------------------------[Microsoft][ODBC SQL Server Driver]Operation canceledhttp://senthilnagore.blogspot.com/ |
|
|
|
themoney32
Starting Member
20 Posts |
Posted - 2009-09-21 : 02:29:50
|
| I dont know exactly how to put that down? I have the columns to display as in CustomerID, EmployeeID, and OrderID, is that what I put before the case when statement? also im getting an error when I put the then coulumn name statement at the end |
|
|
|
themoney32
Starting Member
20 Posts |
Posted - 2009-09-21 : 02:54:04
|
| Also doing the way you do that does not show the actual number of days it was late, say for example if I do then <OrderDate> the case just shows the date and not the number of days. |
|
|
|
senthil_nagore
Master Smack Fu Yak Hacker
1007 Posts |
Posted - 2009-09-21 : 03:04:23
|
| post your query!Senthil.C------------------------------------------------------[Microsoft][ODBC SQL Server Driver]Operation canceledhttp://senthilnagore.blogspot.com/ |
|
|
|
themoney32
Starting Member
20 Posts |
Posted - 2009-09-21 : 03:06:49
|
| SELECT DATEDIFF(ShippedDate, OrderDate) > 7 FROM orders o I tried just doing this, which returns 0's and 1's to show if the difference is greater than 7 or not, however, I need a query that will not display any of the 0's and also show the number of days of the difference. Also, if the ShippedDate has a value of null, I need a query that will NOT display that either |
|
|
|
themoney32
Starting Member
20 Posts |
Posted - 2009-09-21 : 03:10:32
|
| I went to using this now....SELECT OrderID, EmployeeID, CustomerID FROM orders o WHERE DATEDIFF(ShippedDate, OrderDate) > 7This narrows my fields down to the ones I want to display, however this does not show the number of days late each shipment is |
|
|
|
themoney32
Starting Member
20 Posts |
Posted - 2009-09-21 : 13:46:41
|
| Any ideas? |
|
|
|
|
Help with Dates