| # name: test/sql/function/date/test_extract_year.test |
| # description: Extract year function |
| # group: [date] |
| |
| statement ok |
| CREATE TABLE dates (d DATE); |
| |
| statement ok |
| CREATE TABLE dates2 (d DATE); |
| |
| statement ok |
| INSERT INTO dates SELECT date '1970-01-01' + INTERVAL(x) years AS d from table(system_range(0, 429)); |
| |
| statement ok |
| INSERT INTO dates2 SELECT date '1970-01-01' + INTERVAL(x * 6) months AS d from table(system_range(0, 199)); |
| |
| query I |
| SELECT EXTRACT(year FROM d) FROM dates ORDER BY 1 |
| ---- |
| 1970 |
| 1971 |
| 1972 |
| 1973 |
| 1974 |
| 1975 |
| 1976 |
| 1977 |
| 1978 |
| 1979 |
| 1980 |
| 1981 |
| 1982 |
| 1983 |
| 1984 |
| 1985 |
| 1986 |
| 1987 |
| 1988 |
| 1989 |
| 1990 |
| 1991 |
| 1992 |
| 1993 |
| 1994 |
| 1995 |
| 1996 |
| 1997 |
| 1998 |
| 1999 |
| 2000 |
| 2001 |
| 2002 |
| 2003 |
| 2004 |
| 2005 |
| 2006 |
| 2007 |
| 2008 |
| 2009 |
| 2010 |
| 2011 |
| 2012 |
| 2013 |
| 2014 |
| 2015 |
| 2016 |
| 2017 |
| 2018 |
| 2019 |
| 2020 |
| 2021 |
| 2022 |
| 2023 |
| 2024 |
| 2025 |
| 2026 |
| 2027 |
| 2028 |
| 2029 |
| 2030 |
| 2031 |
| 2032 |
| 2033 |
| 2034 |
| 2035 |
| 2036 |
| 2037 |
| 2038 |
| 2039 |
| 2040 |
| 2041 |
| 2042 |
| 2043 |
| 2044 |
| 2045 |
| 2046 |
| 2047 |
| 2048 |
| 2049 |
| 2050 |
| 2051 |
| 2052 |
| 2053 |
| 2054 |
| 2055 |
| 2056 |
| 2057 |
| 2058 |
| 2059 |
| 2060 |
| 2061 |
| 2062 |
| 2063 |
| 2064 |
| 2065 |
| 2066 |
| 2067 |
| 2068 |
| 2069 |
| 2070 |
| 2071 |
| 2072 |
| 2073 |
| 2074 |
| 2075 |
| 2076 |
| 2077 |
| 2078 |
| 2079 |
| 2080 |
| 2081 |
| 2082 |
| 2083 |
| 2084 |
| 2085 |
| 2086 |
| 2087 |
| 2088 |
| 2089 |
| 2090 |
| 2091 |
| 2092 |
| 2093 |
| 2094 |
| 2095 |
| 2096 |
| 2097 |
| 2098 |
| 2099 |
| 2100 |
| 2101 |
| 2102 |
| 2103 |
| 2104 |
| 2105 |
| 2106 |
| 2107 |
| 2108 |
| 2109 |
| 2110 |
| 2111 |
| 2112 |
| 2113 |
| 2114 |
| 2115 |
| 2116 |
| 2117 |
| 2118 |
| 2119 |
| 2120 |
| 2121 |
| 2122 |
| 2123 |
| 2124 |
| 2125 |
| 2126 |
| 2127 |
| 2128 |
| 2129 |
| 2130 |
| 2131 |
| 2132 |
| 2133 |
| 2134 |
| 2135 |
| 2136 |
| 2137 |
| 2138 |
| 2139 |
| 2140 |
| 2141 |
| 2142 |
| 2143 |
| 2144 |
| 2145 |
| 2146 |
| 2147 |
| 2148 |
| 2149 |
| 2150 |
| 2151 |
| 2152 |
| 2153 |
| 2154 |
| 2155 |
| 2156 |
| 2157 |
| 2158 |
| 2159 |
| 2160 |
| 2161 |
| 2162 |
| 2163 |
| 2164 |
| 2165 |
| 2166 |
| 2167 |
| 2168 |
| 2169 |
| 2170 |
| 2171 |
| 2172 |
| 2173 |
| 2174 |
| 2175 |
| 2176 |
| 2177 |
| 2178 |
| 2179 |
| 2180 |
| 2181 |
| 2182 |
| 2183 |
| 2184 |
| 2185 |
| 2186 |
| 2187 |
| 2188 |
| 2189 |
| 2190 |
| 2191 |
| 2192 |
| 2193 |
| 2194 |
| 2195 |
| 2196 |
| 2197 |
| 2198 |
| 2199 |
| 2200 |
| 2201 |
| 2202 |
| 2203 |
| 2204 |
| 2205 |
| 2206 |
| 2207 |
| 2208 |
| 2209 |
| 2210 |
| 2211 |
| 2212 |
| 2213 |
| 2214 |
| 2215 |
| 2216 |
| 2217 |
| 2218 |
| 2219 |
| 2220 |
| 2221 |
| 2222 |
| 2223 |
| 2224 |
| 2225 |
| 2226 |
| 2227 |
| 2228 |
| 2229 |
| 2230 |
| 2231 |
| 2232 |
| 2233 |
| 2234 |
| 2235 |
| 2236 |
| 2237 |
| 2238 |
| 2239 |
| 2240 |
| 2241 |
| 2242 |
| 2243 |
| 2244 |
| 2245 |
| 2246 |
| 2247 |
| 2248 |
| 2249 |
| 2250 |
| 2251 |
| 2252 |
| 2253 |
| 2254 |
| 2255 |
| 2256 |
| 2257 |
| 2258 |
| 2259 |
| 2260 |
| 2261 |
| 2262 |
| 2263 |
| 2264 |
| 2265 |
| 2266 |
| 2267 |
| 2268 |
| 2269 |
| 2270 |
| 2271 |
| 2272 |
| 2273 |
| 2274 |
| 2275 |
| 2276 |
| 2277 |
| 2278 |
| 2279 |
| 2280 |
| 2281 |
| 2282 |
| 2283 |
| 2284 |
| 2285 |
| 2286 |
| 2287 |
| 2288 |
| 2289 |
| 2290 |
| 2291 |
| 2292 |
| 2293 |
| 2294 |
| 2295 |
| 2296 |
| 2297 |
| 2298 |
| 2299 |
| 2300 |
| 2301 |
| 2302 |
| 2303 |
| 2304 |
| 2305 |
| 2306 |
| 2307 |
| 2308 |
| 2309 |
| 2310 |
| 2311 |
| 2312 |
| 2313 |
| 2314 |
| 2315 |
| 2316 |
| 2317 |
| 2318 |
| 2319 |
| 2320 |
| 2321 |
| 2322 |
| 2323 |
| 2324 |
| 2325 |
| 2326 |
| 2327 |
| 2328 |
| 2329 |
| 2330 |
| 2331 |
| 2332 |
| 2333 |
| 2334 |
| 2335 |
| 2336 |
| 2337 |
| 2338 |
| 2339 |
| 2340 |
| 2341 |
| 2342 |
| 2343 |
| 2344 |
| 2345 |
| 2346 |
| 2347 |
| 2348 |
| 2349 |
| 2350 |
| 2351 |
| 2352 |
| 2353 |
| 2354 |
| 2355 |
| 2356 |
| 2357 |
| 2358 |
| 2359 |
| 2360 |
| 2361 |
| 2362 |
| 2363 |
| 2364 |
| 2365 |
| 2366 |
| 2367 |
| 2368 |
| 2369 |
| 2370 |
| 2371 |
| 2372 |
| 2373 |
| 2374 |
| 2375 |
| 2376 |
| 2377 |
| 2378 |
| 2379 |
| 2380 |
| 2381 |
| 2382 |
| 2383 |
| 2384 |
| 2385 |
| 2386 |
| 2387 |
| 2388 |
| 2389 |
| 2390 |
| 2391 |
| 2392 |
| 2393 |
| 2394 |
| 2395 |
| 2396 |
| 2397 |
| 2398 |
| 2399 |
| |
| query I |
| SELECT EXTRACT(year FROM d) FROM dates2 ORDER BY 1 |
| ---- |
| 1970 |
| 1970 |
| 1971 |
| 1971 |
| 1972 |
| 1972 |
| 1973 |
| 1973 |
| 1974 |
| 1974 |
| 1975 |
| 1975 |
| 1976 |
| 1976 |
| 1977 |
| 1977 |
| 1978 |
| 1978 |
| 1979 |
| 1979 |
| 1980 |
| 1980 |
| 1981 |
| 1981 |
| 1982 |
| 1982 |
| 1983 |
| 1983 |
| 1984 |
| 1984 |
| 1985 |
| 1985 |
| 1986 |
| 1986 |
| 1987 |
| 1987 |
| 1988 |
| 1988 |
| 1989 |
| 1989 |
| 1990 |
| 1990 |
| 1991 |
| 1991 |
| 1992 |
| 1992 |
| 1993 |
| 1993 |
| 1994 |
| 1994 |
| 1995 |
| 1995 |
| 1996 |
| 1996 |
| 1997 |
| 1997 |
| 1998 |
| 1998 |
| 1999 |
| 1999 |
| 2000 |
| 2000 |
| 2001 |
| 2001 |
| 2002 |
| 2002 |
| 2003 |
| 2003 |
| 2004 |
| 2004 |
| 2005 |
| 2005 |
| 2006 |
| 2006 |
| 2007 |
| 2007 |
| 2008 |
| 2008 |
| 2009 |
| 2009 |
| 2010 |
| 2010 |
| 2011 |
| 2011 |
| 2012 |
| 2012 |
| 2013 |
| 2013 |
| 2014 |
| 2014 |
| 2015 |
| 2015 |
| 2016 |
| 2016 |
| 2017 |
| 2017 |
| 2018 |
| 2018 |
| 2019 |
| 2019 |
| 2020 |
| 2020 |
| 2021 |
| 2021 |
| 2022 |
| 2022 |
| 2023 |
| 2023 |
| 2024 |
| 2024 |
| 2025 |
| 2025 |
| 2026 |
| 2026 |
| 2027 |
| 2027 |
| 2028 |
| 2028 |
| 2029 |
| 2029 |
| 2030 |
| 2030 |
| 2031 |
| 2031 |
| 2032 |
| 2032 |
| 2033 |
| 2033 |
| 2034 |
| 2034 |
| 2035 |
| 2035 |
| 2036 |
| 2036 |
| 2037 |
| 2037 |
| 2038 |
| 2038 |
| 2039 |
| 2039 |
| 2040 |
| 2040 |
| 2041 |
| 2041 |
| 2042 |
| 2042 |
| 2043 |
| 2043 |
| 2044 |
| 2044 |
| 2045 |
| 2045 |
| 2046 |
| 2046 |
| 2047 |
| 2047 |
| 2048 |
| 2048 |
| 2049 |
| 2049 |
| 2050 |
| 2050 |
| 2051 |
| 2051 |
| 2052 |
| 2052 |
| 2053 |
| 2053 |
| 2054 |
| 2054 |
| 2055 |
| 2055 |
| 2056 |
| 2056 |
| 2057 |
| 2057 |
| 2058 |
| 2058 |
| 2059 |
| 2059 |
| 2060 |
| 2060 |
| 2061 |
| 2061 |
| 2062 |
| 2062 |
| 2063 |
| 2063 |
| 2064 |
| 2064 |
| 2065 |
| 2065 |
| 2066 |
| 2066 |
| 2067 |
| 2067 |
| 2068 |
| 2068 |
| 2069 |
| 2069 |
