YYYYMMDD date format

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'

The TO_CHAR function has many options for date formatting. You can also use the SQL Standard

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'

The iso format is close. The replace function may be used to remove the '-' characters.

Happily this is the ISO format and is supported by CONVERT

As an alternative you can use STRFTIME:

SELECT wk,
       STRFTIME('%Y%m%d', wk)
 FROM totp
 WHERE singer='Madness'

DATE and TIME Operations

Date and time types

Format a date and time

Specify a date

Match a range of dates

Subtract dates

INTERVAL of time

Components of date

Date functions

Date arithmetics

Select the oldest person

Select a record with the latest date

YYYYMMDD date format

Getting the latest record