YYYYMMDD date format

From SQLZOO
Revision as of 15:37, 17 July 2012 by Marek (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

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 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.

 
 
SELECT
    CONVERT(CHAR(8),wk,112),
    wk,
    song
 FROM totp
 WHERE singer='Madness'
SELECT
    REPLACE(CHAR(wk,iso),'-',''),
    wk,
    song
 FROM totp
 WHERE singer='Madness'
SELECT wk,
       SUBSTR(wk,1,4)||SUBSTR(wk,6,2)||SUBSTR(wk,9,2),
       song
 FROM totp
 WHERE singer='Madness'
SELECT
    DATE_FORMAT(wk,'%Y%m%d'),
    wk,
    song
 FROM totp
 WHERE singer='Madness'
SELECT wk,
       TO_CHAR(wk,'YYYYMMDD'),
       song
 FROM totp
 WHERE singer='Madness'
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'
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense