Difference between revisions of "Format dates"

SELECT STRFTIME('%d/%m/%Y', wk), song
 FROM totp
 WHERE singer='Tom Jones'

SELECT CHAR(wk, eur),
       song
 FROM totp
 WHERE singer='Tom Jones'

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'

SELECT FORMAT(wk, 'DD/MM/YYYY'),
       song
 FROM totp
 WHERE singer='Tom Jones'

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'

SELECT TO_CHAR(wk, 'DD/MM/YYYY'), song
FROM gisq.totp
WHERE singer='Tom Jones'

SELECT CONVERT(VARCHAR(10), wk, 103), song
  FROM totp
 WHERE singer='Tom Jones'

SELECT DATE_FORMAT(wk,'%d/%m/%Y'), song
FROM totp
WHERE singer='Tom Jones'

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'

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'

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