how to calculate days between two dates in plsql

Question

Accepted Answer

In PL/SQL, you can calculate the number of days between two dates by simply subtracting one date from another, since Oracle date arithmetic returns the difference in days. The basic syntax is: `days_between := end_date - start_date;`. This returns a numeric value representing the number of days, including fractional parts if times are involved.

For a complete example:
```sql
DECLARE
  start_date DATE := TO_DATE('2024-01-15', 'YYYY-MM-DD');
  end_date DATE := TO_DATE('2024-01-20', 'YYYY-MM-DD');
  days_count NUMBER;
BEGIN
  days_count := end_date - start_date;
  DBMS_OUTPUT.PUT_LINE('Days between: ' || days_count);
END;
```

If you need only whole days, use the `TRUNC()` function to remove the decimal portion: `days_count := TRUNC(end_date - start_date);`. Alternatively, you can use the `ROUND()` function if you want to round to the nearest whole number.

For more complex scenarios, the `FLOOR()` function removes fractional parts downward, and `CEIL()` rounds upward. If you're working with timestamps instead of dates, the same subtraction method works—the result will include fractional days representing hours, minutes, and seconds. When dates include time components, subtracting gives you precise differences, so use `TRUNC()` if you only care about complete days.