Subtract dates

From SQLZOO
Revision as of 21:09, 22 September 2012 by Andr3w (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

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'
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense