Connor at 15:15, 17 July 2012

2012-07-17T15:15:46Z

Connor: Created page with "Format a date and time.

schema:gisq

Many engines support the SQL standard - see Mimer for details.

2012-07-17T15:15:18Z

Created page with "Format a date and time. <div class='ht'> <div class=params>schema:gisq</div> <div> Many engines support the SQL standard - see Mimer for details. </div> <source lang=sql class..."

New page
Format a date and time.
<div class='ht'>
<div class=params>schema:gisq</div>
<div>
Many engines support the SQL standard - see Mimer for details.
</div>
<source lang=sql class='tidy'>
</source>
<source lang=sql class='setup'></source>
<source lang='sql' class='def e-sqlite'>SELECT STRFTIME('%d/%m/%Y', wk), song
FROM totp
WHERE singer='Tom Jones'</source>
<source lang='sql' class='def e-db2'>SELECT CHAR(wk, eur),
song
FROM totp
WHERE singer='Tom Jones'</source>
<source lang='sql' class='def e-ingres'>SELECT CAST(EXTRACT(DAY FROM wk) AS VARCHAR(2))
|| '/' ||
CAST(EXTRACT(MONTH FROM wk) AS VARCHAR(2))
|| '/' ||
CAST(EXTRACT(YEAR FROM wk) AS VARCHAR(4)),
song
FROM totp
WHERE singer='Tom Jones'</source>
<source lang='sql' class='def e-access'>SELECT FORMAT(wk, 'DD/MM/YYYY'),
song
FROM totp
WHERE singer='Tom Jones'</source>
<source lang='sql' class='def e-postgres'>SELECT CAST(EXTRACT(DAY FROM wk) AS VARCHAR(2))
|| '/' ||
CAST(EXTRACT(MONTH FROM wk) AS VARCHAR(2))
|| '/' ||
CAST(EXTRACT(YEAR FROM wk) AS VARCHAR(4)),
song
FROM totp
WHERE singer='Tom Jones'</source>
<source lang='sql' class='def e-oracle'>SELECT TO_CHAR(wk, 'DD/MM/YYYY'), song
FROM totp
WHERE singer='Tom Jones'</source>
<source lang='sql' class='def e-sqlserver'>SELECT CONVERT(VARCHAR(10), wk, 103), song
FROM totp
WHERE singer='Tom Jones'</source>
<source lang='sql' class='def e-mysql'>SELECT DATE_FORMAT(wk,'%d/%m/%Y'), song
FROM totp
WHERE singer='Tom Jones'
</source>
<source lang='sql' class='def e-mimer'>SELECT CAST(EXTRACT(DAY FROM wk) AS VARCHAR(2))
|| '/' ||
CAST(EXTRACT(MONTH FROM wk) AS VARCHAR(2))
|| '/' ||
CAST(EXTRACT(YEAR FROM wk) AS VARCHAR(4)),
song
FROM totp
WHERE singer='Tom Jones'</source>
<source lang='sql' class='def e-sybase'>SELECT CAST(EXTRACT(DAY FROM wk) AS VARCHAR(2))
|| '/' ||
CAST(EXTRACT(MONTH FROM wk) AS VARCHAR(2))
|| '/' ||
CAST(EXTRACT(YEAR FROM wk) AS VARCHAR(4)),
song
FROM totp
WHERE singer='Tom Jones'</source>

<div class="ecomm e-db2" style="display: none">The [[TO_CHAR(dates) |CHAR]] function will convert to any of the following formats: ISO, USA, EUR, JIS, and LOCAL</div>
<div class="ecomm e-access" style="display: none">Use the FORMAT function </div>
<div class="ecomm e-mimer" style="display: none">Create a string representing a date to a particular format The Standard provides us with the EXTRACT function that can be used however many engines provide a more convenient alternative.
EXTRACT EXRESSION as: EXTRACT(MONTH FROM wk)
other components that may be extracted:
YEAR
MONTH
DAY
HOUR
MINUTE
In this example we use CAST in order to build up a date in the UK format dd/mm/yyyy</div>
</div>
{{Functions ref}}

@@ Line 36: / Line 36: @@
   WHERE singer='Tom Jones'</source>
 <source lang='sql' class='def e-oracle'>SELECT TO_CHAR(wk, 'DD/MM/YYYY'), song
-FROM totp
+FROM gisq.totp
 WHERE singer='Tom Jones'</source>
 <source lang='sql' class='def e-sqlserver'>SELECT CONVERT(VARCHAR(10), wk, 103), song