Components of date

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'

...or if you insist on integers instead of strings:

SELECT CAST(STRFTIME('%Y', wk) AS INTEGER),
       CAST(STRFTIME('%m', wk) AS INTEGER),
       CAST(STRFTIME('%d', wk) AS INTEGER)
  FROM totp WHERE song='Rio'

DATE and TIME Operations

Date and time types

Format a date and time

Specify a date

Match a range of dates

Select the oldest person

Select a record with the latest date