Difference between revisions of "EXTRACT"
Line 7: | Line 7: | ||
<tr><td align='left'>oracle</td><td>Yes</td><td>[[TO_CHAR(d,'YYYY')]]</td></tr> | <tr><td align='left'>oracle</td><td>Yes</td><td>[[TO_CHAR(d,'YYYY')]]</td></tr> | ||
<tr><td align='left'>postgres</td><td>Yes</td><td>[[YEAR(d)]]</td></tr> | <tr><td align='left'>postgres</td><td>Yes</td><td>[[YEAR(d)]]</td></tr> | ||
− | <tr><td align='left'>sqlserver</td><td>No</td><td>[ | + | <tr><td align='left'>sqlserver</td><td>No</td><td>[[DATEPART |DATEPART(YEAR, d)]]</td></tr> |
</table> | </table> | ||
<h1>EXTRACT</h1> | <h1>EXTRACT</h1> |
Revision as of 11:34, 13 July 2012
EXTRACT(YEAR FROM d) | ||
---|---|---|
Engine | OK | Alternative |
ingres | Yes | |
mysql | Yes | YEAR(d) |
oracle | Yes | TO_CHAR(d,'YYYY') |
postgres | Yes | YEAR(d) |
sqlserver | No | DATEPART(YEAR, d) |
EXTRACT
EXTRACT allows you to retrieve components of a date.
You can extract also YEAR, MONTH, DAY, HOUR, MINUTE, SECOND.
EXTRACT(YEAR FROM d) EXTRACT(MONTH FROM d) EXTRACT(DAY FROM d) EXTRACT(HOUR FROM d) EXTRACT(MINUTE FROM d) EXTRACT(SECOND FROM d)
In this example you get the year and the hour from the date whn
.
SELECT whn
,DATEPART(YEAR, whn) AS yr
,DATEPART(HOUR, whn) AS hr
FROM eclipse
SELECT whn
,EXTRACT(YEAR FROM td) AS yr
,EXTRACT(HOUR FROM td) AS hr
FROM eclipse
See also