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("Test basic DATE functionality", "[date]") {
9 unique_ptr<QueryResult> result;
10 DuckDB db(nullptr);
11 Connection con(db);
12 con.EnableQueryVerification();
13
14 // create and insert into table
15 REQUIRE_NO_FAIL(con.Query("CREATE TABLE dates(i DATE)"));
16 REQUIRE_NO_FAIL(con.Query("INSERT INTO dates VALUES ('1993-08-14'), (NULL)"));
17
18 // check that we can select dates
19 result = con.Query("SELECT * FROM dates");
20 REQUIRE(result->sql_types[0] == SQLType::DATE);
21 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(Date::FromDate(1993, 8, 14)), Value()}));
22
23 // YEAR function
24 result = con.Query("SELECT year(i) FROM dates");
25 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(1993), Value()}));
26
27 // check that we can convert dates to string
28 result = con.Query("SELECT cast(i AS VARCHAR) FROM dates");
29 REQUIRE(CHECK_COLUMN(result, 0, {Value("1993-08-14"), Value()}));
30
31 // check that we can add days to a date
32 result = con.Query("SELECT i + 5 FROM dates");
33 REQUIRE(result->success);
34 REQUIRE(result->sql_types[0] == SQLType::DATE);
35 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(Date::FromDate(1993, 8, 19)), Value()}));
36
37 // check that we can subtract days from a date
38 result = con.Query("SELECT i - 5 FROM dates");
39 REQUIRE(result->success);
40 REQUIRE(result->sql_types[0] == SQLType::DATE);
41 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(Date::FromDate(1993, 8, 9)), Value()}));
42
43 // HOWEVER, we can't divide or multiply or modulo
44 REQUIRE_FAIL(con.Query("SELECT i * 3 FROM dates"));
45 REQUIRE_FAIL(con.Query("SELECT i / 3 FROM dates"));
46 REQUIRE_FAIL(con.Query("SELECT i % 3 FROM dates"));
47
48 // we also can't add two dates together
49 REQUIRE_FAIL(con.Query("SELECT i + i FROM dates"));
50 // but we can subtract them! resulting in an integer
51 result = con.Query("SELECT (i + 5) - i FROM dates");
52 REQUIRE(result->success);
53 REQUIRE(result->sql_types[0] == SQLType::INTEGER);
54 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(5), Value()}));
55}
56
57TEST_CASE("Test BC dates", "[date]") {
58 unique_ptr<QueryResult> result;
59 DuckDB db(nullptr);
60 Connection con(db);
61 con.EnableQueryVerification();
62
63 // create and insert into table
64 REQUIRE_NO_FAIL(con.Query("CREATE TABLE dates(i DATE)"));
65 REQUIRE_NO_FAIL(con.Query("INSERT INTO dates VALUES ('-1993-08-14'), (NULL)"));
66
67 // check that we can select dates
68 result = con.Query("SELECT * FROM dates");
69 REQUIRE(result->sql_types[0] == SQLType::DATE);
70 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(Date::FromDate(-1993, 8, 14)), Value()}));
71
72 // YEAR function
73 result = con.Query("SELECT year(i) FROM dates");
74 REQUIRE(CHECK_COLUMN(result, 0, {Value::INTEGER(-1993), Value()}));
75
76 // check that we can convert dates to string
77 result = con.Query("SELECT cast(i AS VARCHAR) FROM dates");
78 REQUIRE(CHECK_COLUMN(result, 0, {Value("1993-08-14 (BC)"), Value()}));
79}
80
81TEST_CASE("Test out of range/incorrect date formats", "[date]") {
82 unique_ptr<QueryResult> result;
83 DuckDB db(nullptr);
84 Connection con(db);
85
86 // create and insert into table
87 REQUIRE_NO_FAIL(con.Query("CREATE TABLE dates(i DATE)"));
88 REQUIRE_FAIL(con.Query("INSERT INTO dates VALUES ('blabla')"));
89 // month out of range
90 REQUIRE_FAIL(con.Query("INSERT INTO dates VALUES ('1993-20-14')"));
91 // day out of range
92 REQUIRE_FAIL(con.Query("INSERT INTO dates VALUES ('1993-08-99')"));
93 // day out of range because not a leapyear
94 REQUIRE_FAIL(con.Query("INSERT INTO dates VALUES ('1993-02-29')"));
95 // day out of range because not a leapyear
96 REQUIRE_FAIL(con.Query("INSERT INTO dates VALUES ('1900-02-29')"));
97 // day in range because of leapyear
98 REQUIRE_NO_FAIL(con.Query("INSERT INTO dates VALUES ('1992-02-29')"));
99 // day in range because of leapyear
100 REQUIRE_NO_FAIL(con.Query("INSERT INTO dates VALUES ('2000-02-29')"));
101
102 // test incorrect date formats
103 // dd-mm-YYYY
104 REQUIRE_FAIL(con.Query("INSERT INTO dates VALUES ('02-02-1992')"));
105 // different separators are not supported
106 // REQUIRE_FAIL(con.Query("INSERT INTO dates VALUES ('1900/01/01')"));
107 REQUIRE_FAIL(con.Query("INSERT INTO dates VALUES ('1900a01a01')"));
108 // this should work though
109 REQUIRE_NO_FAIL(con.Query("INSERT INTO dates VALUES ('1900-1-1')"));
110
111 // out of range dates
112 REQUIRE_FAIL(con.Query("INSERT INTO dates VALUES ('-100000000-01-01')"));
113 REQUIRE_FAIL(con.Query("INSERT INTO dates VALUES ('1000000000-01-01')"));
114}
115