Format a date and time

From SQLZOO
Jump to navigation Jump to search

Format a date and time.

schema:gisq

Many engines support the SQL standard - see Mimer for details. 

DROP TABLE t_peep;

CREATE TABLE t_peep
 (id INTEGER PRIMARY KEY
 ,name VARCHAR(50))

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 STRFTIME('%d/%m/%Y', wk), song
 FROM totp
 WHERE singer='Tom Jones'

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

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

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

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

SELECT CHAR(wk, eur),
       song
 FROM totp
 WHERE singer='Tom Jones'
DATE and TIME Operations
DataWars, Data Science Practice Projects - LogoDataWars: Practice Data Science/Analysis with +100 Real Life Projects
Retrieved from "https://sqlzoo.net/w/index.php?title=Format_a_date_and_time&oldid=3314"