Difference between revisions of "Substract dates"

From SQLZOO
Jump to: navigation, search
(Created page with "Subtract dates. <div class='ht'> <div class=params>schema:scott</div> <div> SQL Standard allows dates to be subtracted. We specify the unit and the number of digits of the res...")
 
 
Line 71: Line 71:
 
<div class="ecomm e-mysql" style="display: none">We may use the TO_DAYS function which tells use how many
 
<div class="ecomm e-mysql" style="display: none">We may use the TO_DAYS function which tells use how many
 
days since year 0.
 
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>
6.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.
 
<div class="ecomm e-sqlserver" style="display: none">We can get the answer in days, or in years.

Latest revision as of 10: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'
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense