Difference between revisions of "Components of date"
From SQLZOO
(Created page with "Components of a date: such as the year or the month. <div class='ht'> <div class=params>schema:scott</div> <div> We can extract the year, the month, the day of the month and t...") |
|||
| Line 1: | Line 1: | ||
Components of a date: such as the year or the month. | Components of a date: such as the year or the month. | ||
<div class='ht'> | <div class='ht'> | ||
| − | <div class=params>schema: | + | <div class=params>schema:gisq</div> |
<div> | <div> | ||
We can extract the year, the month, the day of the month and the | We can extract the year, the month, the day of the month and the | ||
Latest revision as of 15:09, 17 July 2012
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'
