---

Return to the SQL Tips

---

Compute Number of days between two dates in SQL/400.

To calculate the number of days between two dates with SQL/400, use the Days scalar function to convert the dates before subtracting. The Days function takes a date argument and returns the number of days since January 1, 0001.

You can use the following SQL statement to produce a table such as the one shown below:

Select   CustID,
         OrderID,
         SaleDate,
         ShipDate,
         ( Days( ShipDate ) -
         Days( SaleDate ) ) As DaysToShip
  From   Sale
  Where  ShipDate Is Not Null
Order By CustID,
         DaysToShip Desc

Be careful not to use an expression like the one below, which results in a date duration:

ShipDate - SaleDate

Subtracting one date from another in SQL/400 produces a date duration, an eight-digit number in the form yyyymmdd. The following examples show the date durations that result when you use the previous expression for sample column values:

Select   CustID,
         OrderID,
         SaleDate,
         ShipDate,
         ( Days( ShipDate ) -
         Days( SaleDate ) ) As DaysToShip
  From   Sale
  Where  ShipDate Is Not Null
Order By CustID,
         DaysToShip Desc

SaleDate      ShipDate     ShipDate - SaleDate
1996-05-01    1996-05-15   14
1995-05-01    1996-05-15   10014 (1 year, 0 months, 14 days)

[report a broken link by clicking here]

---