Andr3w: Created page with "Subtract dates.

schema:scott

SQL Standard allows dates to be subtracted. We specify the unit and the number of digits of the res..."

2012-09-22T21:09:34Z

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..."

New page

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 result answer:
YEAR MONTH DAY HOUR MINUTE SECOND

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

<source lang=sql class='tidy'></source>
<source lang=sql class='setup'></source>

<source lang='sql' class='def e-sqlite'>
SELECT singer, song, wk,
(JULIANDAY(CURRENT_DATE) - JULIANDAY(wk))
FROM totp
WHERE singer = 'Tom Jones'

</source>

<source lang='sql' class='def e-oracle'>
SELECT singer, song, ROUND(SYSDATE - wk) "days"
FROM gisq.totp
WHERE singer = 'Tom Jones'
</source>

<source lang='sql' class='def e-postgres'>
SELECT singer, song,
CURRENT_DATE - wk AS days
FROM totp
WHERE singer = 'Tom Jones'
</source>

<source lang='sql' class='def e-access'>
SELECT singer, song, INT(NOW()- wk) AS "DAYS"
FROM totp
WHERE singer = 'Tom Jones'
</source>

<source lang='sql' class='def e-mysql'>
USE gisq;
SELECT singer, song,
TO_DAYS(NOW()) - TO_DAYS(wk) "DAYS"
FROM totp
WHERE singer = 'Tom Jones'
</source>

<source lang='sql' class='def e-sqlserver'>
SELECT singer, song,
DATEDIFF("d",wk,GETDATE()) "DAYS",
DATEDIFF(yyyy,wk,GETDATE()) "YEARS"
FROM totp
WHERE singer = 'Tom Jones'
</source>

<source lang='sql' class='def'>
SELECT singer, song, wk,
(CURRENT_DATE - wk) DAY(5) "days"
FROM totp
WHERE singer = 'Tom Jones'
</source>

<div class="ecomm e-db2" style="display: none">The [https://aurora.vcu.edu/db2help/db2s0/sqls0415.htm#HDRFNDAYS DAYS] function gives us the number of days since a long time ago.
The string "yymmdd" includes the number of years, months and days since.</div>

<div class="ecomm e-oracle" style="display: none">If we subtract two dates we get the lengths of time measured in days.</div>

<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 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.
[http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_5vxi.asp MSDN documentation on DATEDIFF]</div>
</div>
{{DATE and TIME ref}}

Subtract dates - Revision history

Andr3w: Created page with "Subtract dates. schema:scott SQL Standard allows dates to be subtracted. We specify the unit and the number of digits of the res..."

Andr3w: Created page with "Subtract dates.

schema:scott

SQL Standard allows dates to be subtracted. We specify the unit and the number of digits of the res..."