Difference between revisions of "Subtract dates"

Latest revision as of 21:09, 22 September 2012

Subtract dates.

schema:scott

SQL Standard allows dates to be subtracted. We specify the unit and the number of digits of the result answer: YEAR MONTH DAY HOUR MINUTE SECOND

We want to know how long has it been since Tom Jones featured on TOTP.

SELECT singer, song, wk,
  (JULIANDAY(CURRENT_DATE) - JULIANDAY(wk))
  FROM totp
 WHERE singer = 'Tom Jones'

SELECT singer, song, ROUND(SYSDATE - wk) "days"
  FROM gisq.totp
 WHERE singer = 'Tom Jones'

SELECT singer, song,
  CURRENT_DATE - wk AS days
  FROM totp
 WHERE singer = 'Tom Jones'

SELECT singer, song, INT(NOW()- wk) AS "DAYS"
  FROM totp
 WHERE singer = 'Tom Jones'

USE gisq;
SELECT singer, song,
 TO_DAYS(NOW()) - TO_DAYS(wk) "DAYS"
 FROM totp
WHERE singer = 'Tom Jones'

SELECT singer, song,
       DATEDIFF("d",wk,GETDATE()) "DAYS",
       DATEDIFF(yyyy,wk,GETDATE()) "YEARS"
  FROM totp
 WHERE singer = 'Tom Jones'

SELECT singer, song, wk,
  (CURRENT_DATE - wk) DAY(5) "days"
  FROM totp
 WHERE singer = 'Tom Jones'

Subtract dates

INTERVAL of time