CODEWITHSUNDEEP
sql-servert-sql
user3370044
user3370044

Reputation: 21

How to subtract two dates in SQL Server

I am trying to find how long did it take for each order to be shipped out. Provide the ordID, customer name, and days_to_ship for each order. List them from the longest delay (between order date and ship date) to the shortest.

SELECT OrdID, name, (ShipDate-orderDate) AS Days_to_Ship
    FROM ORDERS, CUSTOMERS
    WHERE ShipDate-OrderDate=(select Max(ShipDate-OrderDate) from orders)
    ORDER BY Days_to_Ship DESC;

Upvotes: 0

Views: 5795

Answers (3)

Mohit
Mohit

Reputation: 11314

You can use the function DATEDIFF 

DATEDIFF(dd, dateTimeField1, dateTimeField2)

where dd is used to get difference in terms of days. you can use others too.

like

datepart       Abbreviation
year           yy, yyyy
quarter        qq, q
month          mm, m
dayofyear      dy, y
day            dd, d
week           wk, ww
weekday        dw, w
hour           hh
minute         mi, n
second         ss, s
millisecond    ms
microsecond    mcs
nanosecond     ns

Upvotes: 0

StuartLC
StuartLC

Reputation: 107237

You haven't specified which RDBMS, but you can use DATEDIFF in MySql and SqlServer (slightly different implementations). MySql's DateDiff returns the difference in days:

select o.OrdID, c.Name, DATEDIFF(o.ShipDate, o.OrderDate) as Days_to_Ship
from ORDERS o 
   INNER JOIN CUSTOMERS c on o.CustomerId = c.CustomerId 
order by Days_to_Ship desc;

For Sql Server you need to specify the interval to DateDiff, and switch the dates around:

select o.OrdID, c.Name, DATEDIFF(dd, o.OrderDate, o.ShipDate) as Days_to_Ship
from ORDERS o 
   INNER JOIN CUSTOMERS c on o.CustomerId = c.CustomerId 
order by Days_to_Ship desc;

I've assumed a join condition between Customers and Orders.

If you just need the longest delay, restrict the list with LIMIT 1 (MySql) or TOP 1 (Sql Server) - together with Order By, you don't need to evaluate a WHERE MAX at all.

Upvotes: 0

sumit
sumit

Reputation: 15464

select 
OrdID,name,DATEDIFF(ShipDate,OrderDATE) as Days_to_Ship
from ORDERS, CUSTOMERS    
order by DATEDIFF(ShipDate,OrderDATE) desc;

Upvotes: 1

Related Questions