1#include "catch.hpp"
2#include "duckdb/common/types/date.hpp"
3#include "test_helpers.hpp"
4
5using namespace duckdb;
6using namespace std;
7
8TEST_CASE("DATE_PART test", "[date]") {
9 unique_ptr<QueryResult> result;
10 DuckDB db(nullptr);
11 Connection con(db);
12 con.EnableQueryVerification();
13
14 REQUIRE_NO_FAIL(con.Query("CREATE TABLE dates(d DATE, s VARCHAR);"));
15 REQUIRE_NO_FAIL(
16 con.Query("INSERT INTO dates VALUES ('1992-01-01', 'year'), ('1992-03-03', 'month'), ('1992-05-05', 'day');"));
17
18 // test date_part with different combinations of constant/non-constant columns
19 result = con.Query("SELECT date_part(NULL::VARCHAR, NULL::TIMESTAMP) FROM dates;");
20 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), Value()}));
21 result = con.Query("SELECT date_part(s, NULL::TIMESTAMP) FROM dates;");
22 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), Value()}));
23
24 // dates
25 result = con.Query("SELECT date_part(NULL, d) FROM dates;");
26 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), Value()}));
27 result = con.Query("SELECT date_part(s, DATE '1992-01-01') FROM dates;");
28 REQUIRE(CHECK_COLUMN(result, 0, {1992, 1, 1}));
29 result = con.Query("SELECT date_part('year', d) FROM dates;");
30 REQUIRE(CHECK_COLUMN(result, 0, {1992, 1992, 1992}));
31 result = con.Query("SELECT date_part(s, d) FROM dates;");
32 REQUIRE(CHECK_COLUMN(result, 0, {1992, 3, 5}));
33
34 // timestamps
35 result = con.Query("SELECT date_part(NULL, d::TIMESTAMP) FROM dates;");
36 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value(), Value()}));
37 result = con.Query("SELECT date_part(s, TIMESTAMP '1992-01-01') FROM dates;");
38 REQUIRE(CHECK_COLUMN(result, 0, {1992, 1, 1}));
39 result = con.Query("SELECT date_part('year', d::TIMESTAMP) FROM dates;");
40 REQUIRE(CHECK_COLUMN(result, 0, {1992, 1992, 1992}));
41 result = con.Query("SELECT date_part(s, d::TIMESTAMP) FROM dates;");
42 REQUIRE(CHECK_COLUMN(result, 0, {1992, 3, 5}));
43
44 // last_day
45 result = con.Query("SELECT LAST_DAY(DATE '1900-02-12'), LAST_DAY(DATE '1992-02-12'), LAST_DAY(DATE '2000-02-12');");
46 REQUIRE(CHECK_COLUMN(result, 0, {Date::FromDate(1900, 2, 28)}));
47 REQUIRE(CHECK_COLUMN(result, 1, {Date::FromDate(1992, 2, 29)}));
48 REQUIRE(CHECK_COLUMN(result, 2, {Date::FromDate(2000, 2, 29)}));
49 result = con.Query("SELECT LAST_DAY(d) FROM dates;");
50 REQUIRE(CHECK_COLUMN(result, 0,
51 {Date::FromDate(1992, 1, 31), Date::FromDate(1992, 3, 31), Date::FromDate(1992, 5, 31)}));
52 result = con.Query("SELECT LAST_DAY(d::timestamp) FROM dates;");
53 REQUIRE(CHECK_COLUMN(result, 0,
54 {Date::FromDate(1992, 1, 31), Date::FromDate(1992, 3, 31), Date::FromDate(1992, 5, 31)}));
55
56 // monthname
57 result = con.Query("SELECT MONTHNAME(d) FROM dates;");
58 REQUIRE(CHECK_COLUMN(result, 0, {"January", "March", "May"}));
59
60 // dayname
61 result = con.Query("SELECT DAYNAME(d) FROM dates;");
62 REQUIRE(CHECK_COLUMN(result, 0, {"Wednesday", "Tuesday", "Tuesday"}));
63
64 // yearweek
65 result = con.Query("SELECT YEARWEEK(d) FROM dates;");
66 REQUIRE(CHECK_COLUMN(result, 0, {199201, 199209, 199218}));
67
68 // aliases
69 result = con.Query("SELECT DAYOFMONTH(d) FROM dates;");
70 REQUIRE(CHECK_COLUMN(result, 0, {1, 3, 5}));
71 result = con.Query("SELECT WEEKDAY(d) FROM dates;");
72 REQUIRE(CHECK_COLUMN(result, 0, {3, 2, 2}));
73 result = con.Query("SELECT WEEKOFYEAR(d) FROM dates;");
74 REQUIRE(CHECK_COLUMN(result, 0, {1, 9, 18}));
75}
76