Difference between revisions of "DATEPART"

From SQLZOO
Jump to: navigation, search
 
(10 intermediate revisions by one user not shown)
Line 3: Line 3:
 
<tr><th colspan='3'>DATEPART(YEAR, d)</th></tr>
 
<tr><th colspan='3'>DATEPART(YEAR, d)</th></tr>
 
<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>No</td><td>[[EXTRACT(YEAR FROM d)]]</td></tr>
+
<tr><td align='left'>ingres</td><td>No</td><td>[[EXTRACT|EXTRACT(YEAR FROM d)]]</td></tr>
<tr><td align='left'>mysql</td><td>No</td><td>[[EXTRACT(YEAR FROM d)]]</td></tr>
+
<tr><td align='left'>mysql</td><td>No</td><td>[[EXTRACT|EXTRACT(YEAR FROM d)]]</td></tr>
<tr><td align='left'>oracle</td><td>No</td><td>[[EXTRACT(YEAR FROM d]])</td></tr>
+
<tr><td align='left'>oracle</td><td>No</td><td>[[EXTRACT|EXTRACT(YEAR FROM d)]]</td></tr>
<tr><td align='left'>postgres</td><td>No</td><td>[[EXTRACT(YEAR FROM d)]]</td></tr>
+
<tr><td align='left'>postgres</td><td>No</td><td>[[EXTRACT|EXTRACT(YEAR FROM d)]]</td></tr>
<tr><td align='left'>sqlserver</td><td>Yes</td><td></td></tr>
+
<tr><td align='left'>sqlserver</td><td>Yes</td><td>DATEPART(YEAR, d)</td></tr>
 
</table>
 
</table>
 +
 
<h1>DATEPART</h1>
 
<h1>DATEPART</h1>
 
<p>DATEPART allows you to retrieve components of a date.</p>
 
<p>DATEPART allows you to retrieve components of a date.</p>
Line 19: Line 20:
  
 
<div class='ht'>
 
<div class='ht'>
 +
<div class=params>schema:gisq</div>
 
In this example you get the year and the month from the date <code>whn</code>.
 
In this example you get the year and the month from the date <code>whn</code>.
 
<source lang='sql' class='def e-sqlserver'>
 
<source lang='sql' class='def e-sqlserver'>
Line 26: Line 28:
 
   FROM eclipse
 
   FROM eclipse
 
</source>  
 
</source>  
 +
<source lang ='sql' class='def e-oracle'>SELECT whn
 +
      ,EXTRACT(YEAR FROM td)  AS yr
 +
      ,EXTRACT(MONTH FROM td) AS mnth
 +
  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
       ,EXTRACT(MONTH FROM td) AS hr
+
       ,EXTRACT(MONTH FROM td) AS mnth
 
   FROM eclipse   
 
   FROM eclipse   
 
</source>
 
</source>
Line 35: Line 42:
 
<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 18:51, 21 October 2012

Compatibility
DATEPART(YEAR, d)
EngineOKAlternative
ingresNoEXTRACT(YEAR FROM d)
mysqlNoEXTRACT(YEAR FROM d)
oracleNoEXTRACT(YEAR FROM d)
postgresNoEXTRACT(YEAR FROM d)
sqlserverYesDATEPART(YEAR, d)

DATEPART

DATEPART allows you to retrieve components of a date.

You can extract also YEAR, MONTH, DAY, HOUR, MINUTE, SECOND.

   DATEPART(YEAR, d)    DATEPART(MONTH, d)
   DATEPART(DAY, d)     DATEPART(HOUR, d)
   DATEPART(MINUTE, d)  DATEPART(SECOND, d) 
schema:gisq

In this example you get the year and the month from the date whn.

SELECT whn
      ,DATEPART(YEAR, whn)  AS yr
      ,DATEPART(MONTH, whn) AS mnth
  FROM eclipse
SELECT whn
      ,EXTRACT(YEAR FROM td)  AS yr
      ,EXTRACT(MONTH FROM td) AS mnth
  FROM gisq.eclipse
SELECT whn
      ,EXTRACT(YEAR FROM td)  AS yr
      ,EXTRACT(MONTH FROM td) AS mnth
  FROM eclipse

See also

Language: English  • Deutsch
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense