Marek: Created page with "How to format a date in the like yyyymmdd.

schema:gisq

Display the dates that Madness played Top of the Pops, show the dates in ..."

2012-07-17T15:37:41Z

Created page with "How to format a date in the like yyyymmdd. <div class='ht'> <div class=params>schema:gisq</div> <div> Display the dates that Madness played Top of the Pops, show the dates in ..."

New page

How to format a date in the like yyyymmdd.
<div class='ht'>
<div class=params>schema:gisq</div>
<div>
Display the dates that Madness played Top of the Pops, show the
dates in the format yyyymmdd.

The SQL standard gives us the EXTRACT function to get year, month and day.
We can use the LPAD function to pad these numbers to 2 digits.

</div>

<source lang=sql class='tidy'></source>
<source lang=sql class='setup'></source>

<source lang='sql' class='def e-sqlserver e-sybase'>
SELECT
CONVERT(CHAR(8),wk,112),
wk,
song
FROM totp
WHERE singer='Madness'
</source>

<source lang='sql' class='def e-db2'>
SELECT
REPLACE(CHAR(wk,iso),'-',''),
wk,
song
FROM totp
WHERE singer='Madness'
</source>

<source lang='sql' class='def e-sqlite'>
SELECT wk,
SUBSTR(wk,1,4)||SUBSTR(wk,6,2)||SUBSTR(wk,9,2),
song
FROM totp
WHERE singer='Madness'
</source>

<source lang='sql' class='def e-mysql'>
SELECT
DATE_FORMAT(wk,'%Y%m%d'),
wk,
song
FROM totp
WHERE singer='Madness'
</source>

<source lang='sql' class='def e-oracle'>
SELECT wk,
TO_CHAR(wk,'YYYYMMDD'),
song
FROM totp
WHERE singer='Madness'
</source>

<source lang='sql' class='def'>
SELECT wk,
CAST(EXTRACT(YEAR FROM wk) AS VARCHAR(4))
||
LPAD(EXTRACT(MONTH FROM wk),2,'0')
||
LPAD(EXTRACT(DAY FROM wk),2,'0') x,
song
FROM totp
WHERE singer='Madness'
</source>

<div class="ecomm e-oracle" style="display: none">The TO_CHAR function has many options for date formatting.
You can also use the SQL Standard <pre>SELECT wk,
CAST(EXTRACT(YEAR FROM wk) AS VARCHAR(4))
||
LPAD(EXTRACT(MONTH FROM wk),2,'0')
||
LPAD(EXTRACT(DAY FROM wk),2,'0') x,
song
FROM totp
WHERE singer='Madness'
</pre></div>

<div class="ecomm e-db2" style="display: none">The iso format is close. The replace function may be used to remove the '-' characters.</div>

<div class="ecomm e-sqlserver e-sybase" style="display: none">
Happily this is the ISO format and is supported by [http://msdn2.microsoft.com/en-us/library/ms187928(SQL.90).aspx CONVERT]
</div>

<div class="ecomm e-sqlite" style="display: none">
As an alternative you can use STRFTIME:
<pre>
SELECT wk,
STRFTIME('%Y%m%d', wk)
FROM totp
WHERE singer='Madness'
</pre>
</div>

</div>
{{DATE and TIME ref}}

YYYYMMDD date format - Revision history

Marek: Created page with "How to format a date in the like yyyymmdd. schema:gisq Display the dates that Madness played Top of the Pops, show the dates in ..."

Marek: Created page with "How to format a date in the like yyyymmdd.

schema:gisq

Display the dates that Madness played Top of the Pops, show the dates in ..."