Components of date

From SQLZoo
Jump to navigation Jump to search

Components of a date: such as the year or the month.

schema:gisq

We can extract the year, the month, the day of the month and the day of the week. Times may be extracted in a similar way. The SQL Standard specifies the understandably unpopular EXTRACT function.

SELECT YEAR(wk), MONTH(wk),
    DAYOFMONTH(wk), DAYNAME(wk)
FROM totp WHERE song='Rio'
SELECT song, singer,
    TO_CHAR(wk,'YYYY') Year,
    TO_CHAR(wk,'MM') Month,
    TO_CHAR(wk,'DD') Day,
    TO_CHAR(wk,'DY') "Week Day",
    TO_CHAR(wk,'DD/MM/YY') "Full"
FROM gisq.totp WHERE song='Rio'
SELECT song, singer,
  YEAR(wk), MONTH(wk),
  DAY(wk), MONTHNAME(wk)
  FROM totp WHERE song='Rio'
SELECT YEAR(wk), MONTH(wk),
       DAY(wk), DATEPART(DW,wk)
FROM totp WHERE song='Rio'
SELECT YEAR(wk), MONTH(wk),
    DAY(wk), WEEKDAY(wk)
FROM totp WHERE song='Rio'
SELECT DATE_PART('YEAR',wk),
       DATE_PART('MONTH',wk),
       DATE_PART('DAY',wk),
       DATE_PART('DOW',wk)
FROM totp WHERE song='Rio'
SELECT STRFTIME('%Y', wk),
       STRFTIME('%m', wk),
       STRFTIME('%d', wk)
  FROM totp WHERE song='Rio'
SELECT EXTRACT(YEAR FROM wk),
       EXTRACT(MONTH FROM wk),
       EXTRACT(DAY FROM wk)
  FROM totp WHERE song='Rio'
DataWars, Data Science Practice Projects - LogoDataWars: Practice Data Science/Analysis with +100 Real Life Projects