Difference between revisions of "EXTRACT"

From SQLZOO
Jump to: navigation, search
 
(3 intermediate revisions by one user not shown)
Line 4: Line 4:
 
<tr><td align='center'>'''Engine'''</td><td align='center'>'''OK'''</td><td align='center'>'''Alternative'''</td></tr>
 
<tr><td align='center'>'''Engine'''</td><td align='center'>'''OK'''</td><td align='center'>'''Alternative'''</td></tr>
 
<tr><td align='left'>ingres</td><td>Yes</td><td></td></tr>
 
<tr><td align='left'>ingres</td><td>Yes</td><td></td></tr>
<tr><td align='left'>mysql</td><td>Yes</td><td>[[YEAR(d)]]</td></tr>
+
<tr><td align='left'>mysql</td><td>Yes</td><td>[[YEAR |YEAR(d)]]</td></tr>
<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(dates) |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 |YEAR(d)]]</td></tr>
<tr><td align='left'>sqlserver</td><td>No</td><td>[http://sqlzoo.net/w/index.php/DATEPART DATEPART(YEAR, d)]</td></tr>
+
<tr><td align='left'>sqlserver</td><td>No</td><td>[[DATEPART |DATEPART(YEAR, d)]]</td></tr>
 
</table>
 
</table>
 +
 
<h1>EXTRACT</h1>
 
<h1>EXTRACT</h1>
 
<p>EXTRACT allows you to retrieve components of a date.</p>
 
<p>EXTRACT allows you to retrieve components of a date.</p>
Line 26: Line 27:
 
   FROM eclipse
 
   FROM eclipse
 
</source>  
 
</source>  
 +
<source lang ='sql' class='def e-oracle'>SELECT whn
 +
      ,EXTRACT(YEAR FROM td)  AS yr
 +
      ,EXTRACT(HOUR FROM td) AS hr
 +
  FROM gisq.eclipse 
 +
</source>
 
<source lang ='sql' class='def'>SELECT whn
 
<source lang ='sql' class='def'>SELECT whn
 
       ,EXTRACT(YEAR FROM td)  AS yr
 
       ,EXTRACT(YEAR FROM td)  AS yr
Line 35: Line 41:
 
<p>See also</p>
 
<p>See also</p>
 
<ul>
 
<ul>
   <li>[[+ date]]</li>
+
   <li>[[%2B(dates) |+ date]]</li>
 
</ul>
 
</ul>
 +
 +
{{Languages}}

Latest revision as of 21:55, 21 October 2012

Compatibility
EXTRACT(YEAR FROM d)
EngineOKAlternative
ingresYes
mysqlYesYEAR(d)
oracleYesTO_CHAR(d,'YYYY')
postgresYesYEAR(d)
sqlserverNoDATEPART(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 gisq.eclipse
SELECT whn
      ,EXTRACT(YEAR FROM td)  AS yr
      ,EXTRACT(HOUR FROM td) AS hr
  FROM eclipse

See also

Language: English  • Deutsch
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense