Difference between revisions of "TO CHAR(dates)"
From SQLZOO
(Created page with "<table align='right' border='1'> <caption>Compatibility</caption> <tr><th colspan='3'>TO_CHAR(d,'YYYY')</th></tr> <tr><td align='center'>'''Engine'''</td><td align='center'>''...") |
|||
| (One intermediate revision by one user not shown) | |||
| Line 25: | Line 25: | ||
<source lang='sql' class='def e-sqlserver'> | <source lang='sql' class='def e-sqlserver'> | ||
SELECT DATEPART(YEAR from whn) AS v | SELECT DATEPART(YEAR from whn) AS v | ||
| − | |||
,whn | ,whn | ||
,wht | ,wht | ||
| Line 37: | Line 36: | ||
</source> | </source> | ||
<source lang='sql' class='def'> | <source lang='sql' class='def'> | ||
| − | SELECT EXTRACT(YEAR from whn) AS v, | + | SELECT EXTRACT(YEAR from whn) AS v, whn, wht |
| − | + | ||
FROM eclipse | FROM eclipse | ||
</source> | </source> | ||
| Line 46: | Line 44: | ||
<ul> | <ul> | ||
<li>[[EXTRACT |EXTRACT function]]</li> | <li>[[EXTRACT |EXTRACT function]]</li> | ||
| − | <li>[[ +(date) function]]</li> | + | <li>[[%2B(dates) |+(date) function]]</li> |
<li>[[HOUR |HOUR function]]</li> | <li>[[HOUR |HOUR function]]</li> | ||
<li>[[MINUTE |MINUTE function]]</li> | <li>[[MINUTE |MINUTE function]]</li> | ||
Latest revision as of 15:28, 16 July 2012
| TO_CHAR(d,'YYYY') | ||
|---|---|---|
| Engine | OK | Alternative |
| ingres | No | EXTRACT(HOUR from d) |
| mysql | No | EXTRACT(HOUR from d) |
| oracle | Yes | |
| postgres | No | EXTRACT(HOUR from d) |
| sqlserver | No | DATEPART(HOUR,d) |
TO_CHAR (dates)
TO_CHAR allows you to convert a date to a string using a variety of formats.
TO_CHAR(d, 'YYYY') -> Four digit year TO_CHAR(d, 'MM') -> Two digit month TO_CHAR(d, 'DD') -> Two digit day TO_CHAR(d, 'HH24') -> Two digit hour TO_CHAR(d, 'MI') -> Two digit minutes TO_CHAR(d, 'MON') -> Three character month
In this example you get the month as a three character abbreviation and a four digit year from the date field whn.
SELECT DATEPART(YEAR FROM whn) AS v ,whn ,wht FROM eclipse
SELECT TO_CHAR(whn, 'MON YYYY') AS v ,whn ,wht FROM gisq.eclipse
SELECT EXTRACT(YEAR FROM whn) AS v, whn, wht FROM eclipse
See also
