Components of date

From SQLZOO
Jump to: navigation, 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'
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense