Difference between revisions of "Substract dates"

Latest revision as of 09:10, 18 July 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'

@@ Line 71: / Line 71: @@
 <div class="ecomm e-mysql" style="display: none">We may use the TO_DAYS function which tells use how many
 days since year 0.
-See [http://www.mysql.com/doc/en/Date_and_time_functions.html
+See [http://www.mysql.com/doc/en/Date_and_time_functions.html Date and Time Functions] for more details.</div>
-.3.4 Date and Time Functions] for more details.</div>
 <div class="ecomm e-sqlserver" style="display: none">We can get the answer in days, or in years.