SQLzoo.net

How to format a date in the like yyyymmdd

Oracle

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.



Specific to Oracle
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'

Related links:

  • DATES