github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/expression/temporal_interval.result (about) 1 select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000 99:99:99.999999" DAY_MICROSECOND); 2 date_add("1997-12-31 23:59:59.000002",INTERVAL "10000 99:99:99.999999" DAY_MICROSECOND) 3 2025-05-23 04:40:39.000001000 4 select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000:99:99.999999" HOUR_MICROSECOND); 5 date_add("1997-12-31 23:59:59.000002",INTERVAL "10000:99:99.999999" HOUR_MICROSECOND) 6 1999-02-21 17:40:39.000001000 7 select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000:99.999999" MINUTE_MICROSECOND); 8 date_add("1997-12-31 23:59:59.000002",INTERVAL "10000:99.999999" MINUTE_MICROSECOND) 9 1998-01-07 22:41:39.000001000 10 select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000.999999" SECOND_MICROSECOND); 11 date_add("1997-12-31 23:59:59.000002",INTERVAL "10000.999999" SECOND_MICROSECOND) 12 1998-01-01 02:46:40.000001000 13 select date_add("1997-12-31 23:59:59.000002",INTERVAL "999999" MICROSECOND); 14 date_add("1997-12-31 23:59:59.000002",INTERVAL "999999" MICROSECOND) 15 1998-01-01 00:00:00.000001000 16 select date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND); 17 date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND) 18 1998-01-01 00:00:00 19 select date_add("1997-12-31 23:59:59",INTERVAL 1 MINUTE); 20 date_add("1997-12-31 23:59:59",INTERVAL 1 MINUTE) 21 1998-01-01 00:00:59 22 select date_add("1997-12-31 23:59:59",INTERVAL 1 HOUR); 23 date_add("1997-12-31 23:59:59",INTERVAL 1 HOUR) 24 1998-01-01 00:59:59 25 select date_add("1997-12-31 23:59:59",INTERVAL 1 DAY); 26 date_add("1997-12-31 23:59:59",INTERVAL 1 DAY) 27 1998-01-01 23:59:59 28 select date_add("1997-12-31 23:59:59",INTERVAL 0 DAY); 29 date_add("1997-12-31 23:59:59",INTERVAL 0 DAY) 30 1997-12-31 23:59:59 31 select date_add("1997-12-31 23:59:59",INTERVAL 1 MONTH); 32 date_add("1997-12-31 23:59:59",INTERVAL 1 MONTH) 33 1998-01-31 23:59:59 34 select date_add("1997-12-31 23:59:59",INTERVAL 1 QUARTER); 35 date_add("1997-12-31 23:59:59",INTERVAL 1 QUARTER) 36 1998-03-31 23:59:59 37 select date_add("1997-12-31 23:59:59",INTERVAL 1 YEAR); 38 date_add("1997-12-31 23:59:59",INTERVAL 1 YEAR) 39 1998-12-31 23:59:59 40 select date_add("1997-12-31 23:59:59",INTERVAL "1:1" MINUTE_SECOND); 41 date_add("1997-12-31 23:59:59",INTERVAL "1:1" MINUTE_SECOND) 42 1998-01-01 00:01:00 43 select date_add("1997-12-31 23:59:59",INTERVAL "1:1" HOUR_MINUTE); 44 date_add("1997-12-31 23:59:59",INTERVAL "1:1" HOUR_MINUTE) 45 1998-01-01 01:00:59 46 select date_add("1997-12-31 23:59:59",INTERVAL "1:1" DAY_HOUR); 47 date_add("1997-12-31 23:59:59",INTERVAL "1:1" DAY_HOUR) 48 1998-01-02 00:59:59 49 select date_add("1997-12-31 23:59:59",INTERVAL "1 1" YEAR_MONTH); 50 date_add("1997-12-31 23:59:59",INTERVAL "1 1" YEAR_MONTH) 51 1999-01-31 23:59:59 52 select date_add("1997-12-31 23:59:59",INTERVAL "1:1:1" HOUR_SECOND); 53 date_add("1997-12-31 23:59:59",INTERVAL "1:1:1" HOUR_SECOND) 54 1998-01-01 01:01:00 55 select date_add("1997-12-31 23:59:59",INTERVAL "1 1:1" DAY_MINUTE); 56 date_add("1997-12-31 23:59:59",INTERVAL "1 1:1" DAY_MINUTE) 57 1998-01-02 01:00:59 58 select date_add("1997-12-31 23:59:59",INTERVAL "1 1:1:1" DAY_SECOND); 59 date_add("1997-12-31 23:59:59",INTERVAL "1 1:1:1" DAY_SECOND) 60 1998-01-02 01:01:00 61 select date_add("1997-12-31 23:59:59",INTERVAL 100000 SECOND); 62 date_add("1997-12-31 23:59:59",INTERVAL 100000 SECOND) 63 1998-01-02 03:46:39 64 select date_add("1997-12-31 23:59:59",INTERVAL -100000 MINUTE); 65 date_add("1997-12-31 23:59:59",INTERVAL -100000 MINUTE) 66 1997-10-23 13:19:59 67 select date_add("1997-12-31 23:59:59",INTERVAL 100000 HOUR); 68 date_add("1997-12-31 23:59:59",INTERVAL 100000 HOUR) 69 2009-05-29 15:59:59 70 select date_add("1997-12-31 23:59:59",INTERVAL -100000 DAY); 71 date_add("1997-12-31 23:59:59",INTERVAL -100000 DAY) 72 1724-03-17 23:59:59 73 select date_add("1997-12-31 23:59:59",INTERVAL 100000 MONTH); 74 Data truncation: data out of range: data type datetime, 75 select date_add("1997-12-31 23:59:59",INTERVAL 100000 QUARTER); 76 Data truncation: data out of range: data type datetime, 77 select date_add("1997-12-31 23:59:59",INTERVAL -100000 YEAR); 78 Data truncation: data out of range: data type datetime, 79 select date_add("1997-12-31 23:59:59",INTERVAL "10000:1" MINUTE_SECOND); 80 date_add("1997-12-31 23:59:59",INTERVAL "10000:1" MINUTE_SECOND) 81 1998-01-07 22:40:00 82 select date_add("1997-12-31 23:59:59",INTERVAL "-10000:1" HOUR_MINUTE); 83 date_add("1997-12-31 23:59:59",INTERVAL "-10000:1" HOUR_MINUTE) 84 1996-11-10 07:58:59 85 select date_add("1997-12-31 23:59:59",INTERVAL "10000:1" DAY_HOUR); 86 date_add("1997-12-31 23:59:59",INTERVAL "10000:1" DAY_HOUR) 87 2025-05-19 00:59:59 88 select date_add("1997-12-31 23:59:59",INTERVAL "-100 1" YEAR_MONTH); 89 date_add("1997-12-31 23:59:59",INTERVAL "-100 1" YEAR_MONTH) 90 1897-11-30 23:59:59 91 select date_add("1997-12-31 23:59:59",INTERVAL "10000:99:99" HOUR_SECOND); 92 date_add("1997-12-31 23:59:59",INTERVAL "10000:99:99" HOUR_SECOND) 93 1999-02-21 17:40:38 94 select date_add("1997-12-31 23:59:59",INTERVAL " -10000 99:99" DAY_MINUTE); 95 date_add("1997-12-31 23:59:59",INTERVAL " -10000 99:99" DAY_MINUTE) 96 1970-08-11 19:20:59 97 select date_add("1997-12-31 23:59:59",INTERVAL "10000 99:99:99" DAY_SECOND); 98 date_add("1997-12-31 23:59:59",INTERVAL "10000 99:99:99" DAY_SECOND) 99 2025-05-23 04:40:38 100 select date_add("1997-12-31",INTERVAL 1 SECOND); 101 date_add("1997-12-31",INTERVAL 1 SECOND) 102 1997-12-31 00:00:01 103 select date_add("1997-12-31",INTERVAL 1 DAY); 104 date_add("1997-12-31",INTERVAL 1 DAY) 105 1998-01-01 00:00:00 106 select date_add(NULL,INTERVAL 100000 SECOND); 107 date_add(NULL,INTERVAL 100000 SECOND) 108 null 109 select date_add("1997-12-31 23:59:59",INTERVAL NULL SECOND); 110 date_add("1997-12-31 23:59:59",INTERVAL NULL SECOND) 111 null 112 select date_add("1997-12-31 23:59:59",INTERVAL NULL MINUTE_SECOND); 113 date_add("1997-12-31 23:59:59",INTERVAL NULL MINUTE_SECOND) 114 null 115 select date_add("9999-12-31 23:59:59",INTERVAL 1 SECOND); 116 Data truncation: data out of range: data type datetime, 117 select date_sub("0000-00-00 00:00:00",INTERVAL 1 SECOND); 118 invalid input: invalid datatime value 0000-00-00 00:00:00 119 select date_add('1998-01-30',Interval 1 month); 120 date_add('1998-01-30',Interval 1 month) 121 1998-02-28 00:00:00 122 select date_add('1998-01-30',Interval '2:1' year_month); 123 date_add('1998-01-30',Interval '2:1' year_month) 124 2000-02-29 00:00:00 125 select date_add('1996-02-29',Interval '1' year); 126 date_add('1996-02-29',Interval '1' year) 127 1997-02-28 00:00:00 128 select date_add('1996-02-29',Interval q year); 129 invalid input: column q does not exist 130 select date_add("1997-12-31 23:59:59",INTERVAL 1.5 SECOND); 131 date_add("1997-12-31 23:59:59",INTERVAL 1.5 SECOND) 132 1998-01-01 00:00:01 133 select date_add("1997-12-31 23:59:59",INTERVAL 1.5 MINUTE); 134 date_add("1997-12-31 23:59:59",INTERVAL 1.5 MINUTE) 135 1998-01-01 00:01:59 136 select date_add("1997-12-31 23:59:59",INTERVAL 1.5 HOUR); 137 date_add("1997-12-31 23:59:59",INTERVAL 1.5 HOUR) 138 1998-01-01 01:59:59 139 select date_add("1997-12-31 23:59:59",INTERVAL 1.5 DAY); 140 date_add("1997-12-31 23:59:59",INTERVAL 1.5 DAY) 141 1998-01-02 23:59:59 142 select date_add("1997-12-31 23:59:59",INTERVAL 1.5 ABC); 143 SQL parser error: You have an error in your SQL syntax; check the manual that corresponds to your MatrixOne server version for the right syntax to use. syntax error at line 1 column 54 near " ABC);"; 144 select date_sub("1998-01-01 00:00:00",INTERVAL 1 SECOND); 145 date_sub("1998-01-01 00:00:00",INTERVAL 1 SECOND) 146 1997-12-31 23:59:59 147 select date_sub("1998-01-01 00:00:00",INTERVAL 1 MINUTE); 148 date_sub("1998-01-01 00:00:00",INTERVAL 1 MINUTE) 149 1997-12-31 23:59:00 150 select date_sub("1998-01-01 00:00:00",INTERVAL 1 HOUR); 151 date_sub("1998-01-01 00:00:00",INTERVAL 1 HOUR) 152 1997-12-31 23:00:00 153 select date_sub("1998-01-01 00:00:00",INTERVAL 1 DAY); 154 date_sub("1998-01-01 00:00:00",INTERVAL 1 DAY) 155 1997-12-31 00:00:00 156 select date_sub("1998-01-01 00:00:00",INTERVAL 1 MONTH); 157 date_sub("1998-01-01 00:00:00",INTERVAL 1 MONTH) 158 1997-12-01 00:00:00 159 select date_sub("1998-01-01 00:00:00",INTERVAL 1 QUARTER); 160 date_sub("1998-01-01 00:00:00",INTERVAL 1 QUARTER) 161 1997-10-01 00:00:00 162 select date_sub("1998-01-01 00:00:00",INTERVAL 1 YEAR); 163 date_sub("1998-01-01 00:00:00",INTERVAL 1 YEAR) 164 1997-01-01 00:00:00 165 select date_sub("1998-01-01 00:00:00",INTERVAL 100000 SECOND); 166 date_sub("1998-01-01 00:00:00",INTERVAL 100000 SECOND) 167 1997-12-30 20:13:20 168 select date_sub("1998-01-01 00:00:009",INTERVAL -100000 MINUTE); 169 date_sub("1998-01-01 00:00:009",INTERVAL -100000 MINUTE) 170 1998-03-11 10:40:09 171 select date_sub("1998-01-01 00:00:00",INTERVAL 100000 HOUR); 172 date_sub("1998-01-01 00:00:00",INTERVAL 100000 HOUR) 173 1986-08-05 08:00:00 174 select date_sub("1998-01-01 00:00:00",INTERVAL 0 HOUR); 175 date_sub("1998-01-01 00:00:00",INTERVAL 0 HOUR) 176 1998-01-01 00:00:00 177 select date_sub("1998-01-01 00:00:00",INTERVAL -100000 DAY); 178 date_sub("1998-01-01 00:00:00",INTERVAL -100000 DAY) 179 2271-10-17 00:00:00 180 select date_sub("1998-01-01 00:00:00",INTERVAL 100000 MONTH); 181 Data truncation: data out of range: data type datetime, 182 select date_sub("1998-01-01 00:00:00",INTERVAL 100000 QUARTER); 183 Data truncation: data out of range: data type datetime, 184 select date_sub("1998-01-01 00:00:00",INTERVAL -100000 YEAR); 185 Data truncation: data out of range: data type datetime, 186 select date_sub("1998-01-01 00:00:00",INTERVAL "1:1" MINUTE_SECOND); 187 date_sub("1998-01-01 00:00:00",INTERVAL "1:1" MINUTE_SECOND) 188 1997-12-31 23:58:59 189 select date_sub("1998-01-01 00:00:00",INTERVAL "1:1" HOUR_MINUTE); 190 date_sub("1998-01-01 00:00:00",INTERVAL "1:1" HOUR_MINUTE) 191 1997-12-31 22:59:00 192 select date_sub("1998-01-01 00:00:00",INTERVAL "1:1" DAY_HOUR); 193 date_sub("1998-01-01 00:00:00",INTERVAL "1:1" DAY_HOUR) 194 1997-12-30 23:00:00 195 select date_sub("1998-01-01 00:00:00",INTERVAL "1 1" YEAR_MONTH); 196 date_sub("1998-01-01 00:00:00",INTERVAL "1 1" YEAR_MONTH) 197 1996-12-01 00:00:00 198 select date_sub("1998-01-01 00:00:00",INTERVAL "1:1:1" HOUR_SECOND); 199 date_sub("1998-01-01 00:00:00",INTERVAL "1:1:1" HOUR_SECOND) 200 1997-12-31 22:58:59 201 select date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1" DAY_MINUTE); 202 date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1" DAY_MINUTE) 203 1997-12-30 22:59:00 204 select date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1:1" DAY_SECOND); 205 date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1:1" DAY_SECOND) 206 1997-12-30 22:58:59 207 select date_sub("1998-01-01 00:00:00",INTERVAL "10000:1" MINUTE_SECOND); 208 date_sub("1998-01-01 00:00:00",INTERVAL "10000:1" MINUTE_SECOND) 209 1997-12-25 01:19:59 210 select date_sub("1998-01-01 00:00:00",INTERVAL "-10000:1" HOUR_MINUTE); 211 date_sub("1998-01-01 00:00:00",INTERVAL "-10000:1" HOUR_MINUTE) 212 1999-02-21 16:01:00 213 select date_sub("1998-01-01 00:00:00",INTERVAL "10000:1" DAY_HOUR); 214 date_sub("1998-01-01 00:00:00",INTERVAL "10000:1" DAY_HOUR) 215 1970-08-15 23:00:00 216 select date_sub("1998-01-01 00:00:00",INTERVAL "-100 1" YEAR_MONTH); 217 date_sub("1998-01-01 00:00:00",INTERVAL "-100 1" YEAR_MONTH) 218 2098-02-01 00:00:00 219 select date_sub("1998-01-01 00:00:00",INTERVAL "10000:99:99" HOUR_SECOND); 220 date_sub("1998-01-01 00:00:00",INTERVAL "10000:99:99" HOUR_SECOND) 221 1996-11-10 06:19:21 222 select date_sub("1998-01-01 00:00:00",INTERVAL " -10000 99:99" DAY_MINUTE); 223 date_sub("1998-01-01 00:00:00",INTERVAL " -10000 99:99" DAY_MINUTE) 224 2025-05-23 04:39:00 225 select date_sub("1998-01-01 00:00:00",INTERVAL "10000 99:99:99" DAY_SECOND); 226 date_sub("1998-01-01 00:00:00",INTERVAL "10000 99:99:99" DAY_SECOND) 227 1970-08-11 19:19:21 228 select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1 1:1:1.000002" DAY_MICROSECOND); 229 date_sub("1998-01-01 00:00:00.000001",INTERVAL "1 1:1:1.000002" DAY_MICROSECOND) 230 1997-12-30 22:58:58.999999000 231 select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1:1.000002" HOUR_MICROSECOND); 232 date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1:1.000002" HOUR_MICROSECOND) 233 1997-12-31 22:58:58.999999000 234 select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1.000002" MINUTE_MICROSECOND); 235 date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1.000002" MINUTE_MICROSECOND) 236 1997-12-31 23:58:58.999999000 237 select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1.000002" SECOND_MICROSECOND); 238 date_sub("1998-01-01 00:00:00.000001",INTERVAL "1.000002" SECOND_MICROSECOND) 239 1997-12-31 23:59:58.999999000 240 select date_sub("1998-01-01 00:00:00.000001",INTERVAL "000002" MICROSECOND); 241 date_sub("1998-01-01 00:00:00.000001",INTERVAL "000002" MICROSECOND) 242 1997-12-31 23:59:59.999999000 243 select date_sub("1998-01-01",INTERVAL 1 SECOND); 244 date_sub("1998-01-01",INTERVAL 1 SECOND) 245 1997-12-31 23:59:59 246 select date_sub("1998-01-01",INTERVAL 1 DAY); 247 date_sub("1998-01-01",INTERVAL 1 DAY) 248 1997-12-31 00:00:00 249 select date_sub(NULL,INTERVAL 100000 SECOND); 250 date_sub(NULL,INTERVAL 100000 SECOND) 251 null 252 select date_sub("1998-01-01 00:00:00",INTERVAL NULL SECOND); 253 date_sub("1998-01-01 00:00:00",INTERVAL NULL SECOND) 254 null 255 select date_sub("1998-01-01 00:00:00",INTERVAL NULL MINUTE_SECOND); 256 date_sub("1998-01-01 00:00:00",INTERVAL NULL MINUTE_SECOND) 257 null 258 select date_sub("1998-01-01 00:00:00",INTERVAL 1 SECOND); 259 date_sub("1998-01-01 00:00:00",INTERVAL 1 SECOND) 260 1997-12-31 23:59:59 261 select date_sub("0000-00-00 00:00:00",INTERVAL 1 SECOND); 262 invalid input: invalid datatime value 0000-00-00 00:00:00 263 select date_add('1998-01-30',Interval 1 month); 264 date_add('1998-01-30',Interval 1 month) 265 1998-02-28 00:00:00 266 select date_sub('1998-02-01',Interval '2:1' year_month); 267 date_sub('1998-02-01',Interval '2:1' year_month) 268 1996-01-01 00:00:00 269 select date_sub('1996-02-29',Interval '1' year); 270 date_sub('1996-02-29',Interval '1' year) 271 1995-02-28 00:00:00 272 select date_add('1996-02-29',Interval a year); 273 invalid input: column a does not exist 274 select date_sub("1998-01-01 00:00:00",INTERVAL 1.5 SECOND); 275 date_sub("1998-01-01 00:00:00",INTERVAL 1.5 SECOND) 276 1997-12-31 23:59:58 277 select date_sub("1998-01-01 00:00:00",INTERVAL 1.5 MINUTE); 278 date_sub("1998-01-01 00:00:00",INTERVAL 1.5 MINUTE) 279 1997-12-31 23:58:00 280 select date_sub("1998-01-01 00:00:00",INTERVAL 1.5 HOUR); 281 date_sub("1998-01-01 00:00:00",INTERVAL 1.5 HOUR) 282 1997-12-31 22:00:00 283 select date_sub("1998-01-01 00:00:00",INTERVAL 1.5 DAY); 284 date_sub("1998-01-01 00:00:00",INTERVAL 1.5 DAY) 285 1997-12-30 00:00:00 286 select date_sub("1998-01-01 00:00:00",INTERVAL 1.5 MONTH); 287 date_sub("1998-01-01 00:00:00",INTERVAL 1.5 MONTH) 288 1997-11-01 00:00:00 289 select date_sub("1998-01-01 00:00:00",INTERVAL 1.5 QUARTER); 290 date_sub("1998-01-01 00:00:00",INTERVAL 1.5 QUARTER) 291 1997-07-01 00:00:00 292 select date_sub("1998-01-01 00:00:00",INTERVAL 1 ABC); 293 SQL parser error: You have an error in your SQL syntax; check the manual that corresponds to your MatrixOne server version for the right syntax to use. syntax error at line 1 column 52 near " ABC);"; 294 select date_sub(NULL,INTERVAL 100000 SECOND); 295 date_sub(NULL,INTERVAL 100000 SECOND) 296 null 297 select date_sub("1998-01-02",INTERVAL 31 DAY); 298 date_sub("1998-01-02",INTERVAL 31 DAY) 299 1997-12-02 00:00:00 300 select "1997-12-31 23:59:59" + INTERVAL 1 SECOND; 301 "1997-12-31 23:59:59" + INTERVAL 1 SECOND 302 1998-01-01 00:00:00 303 select INTERVAL 1 DAY + "1997-12-31"; 304 INTERVAL 1 DAY + "1997-12-31" 305 1998-01-01 00:00:00 306 select "1998-01-01 00:00:00" - INTERVAL 1 SECOND; 307 "1998-01-01 00:00:00" - INTERVAL 1 SECOND 308 1997-12-31 23:59:59 309 SELECT "1900-01-01 00:00:00" + INTERVAL 2147483648 SECOND; 310 "1900-01-01 00:00:00" + INTERVAL 2147483648 SECOND 311 1968-01-20 03:14:08 312 SELECT "1900-01-01 00:00:00" + INTERVAL "1:2147483647" MINUTE_SECOND; 313 "1900-01-01 00:00:00" + INTERVAL "1:2147483647" MINUTE_SECOND 314 1968-01-20 03:15:07 315 SELECT "1900-01-01 00:00:00" + INTERVAL "100000000:214748364700" MINUTE_SECOND; 316 "1900-01-01 00:00:00" + INTERVAL "100000000:214748364700" MINUTE_SECOND 317 8895-03-27 22:11:40 318 SELECT "1900-01-01 00:00:00" + INTERVAL 1<<37 SECOND; 319 "1900-01-01 00:00:00" + INTERVAL 1<<37 SECOND 320 6255-04-08 15:04:32 321 SELECT "1900-01-01 00:00:00" + INTERVAL 1<<31 MINUTE; 322 "1900-01-01 00:00:00" + INTERVAL 1<<31 MINUTE 323 5983-01-24 02:08:00 324 SELECT "1900-01-01 00:00:00" + INTERVAL 1<<20 HOUR; 325 "1900-01-01 00:00:00" + INTERVAL 1<<20 HOUR 326 2019-08-15 16:00:00 327 SELECT "1900-01-01 00:00:00" + INTERVAL 1<<38 SECOND; 328 Data truncation: data out of range: data type datetime, 329 SELECT "1900-01-01 00:00:00" + INTERVAL 1<<33 MINUTE; 330 Data truncation: data out of range: data type datetime, 331 SELECT "1900-01-01 00:00:00" + INTERVAL 1<<30 HOUR; 332 Data truncation: data out of range: data type datetime, 333 SELECT "1900-01-01 00:00:00" + INTERVAL "1000000000:214748364700" MINUTE_SECOND; 334 Data truncation: data out of range: data type datetime, 335 SELECT "1997-12-31 23:59:59" + INTERVAL 1 SECOND; 336 "1997-12-31 23:59:59" + INTERVAL 1 SECOND 337 1998-01-01 00:00:00 338 create table t1(i int,a date,b date,c datetime,d char(20),e varchar(50)); 339 insert into t1 values(1,"1997-12-31","1997-12-31","1997-12-31 23:59:59.000002","1997-12-31 23:59:59","1997-12-31 23:59:59.000002"); 340 insert into t1 values(2,"1998-01-01","1998-01-01","1998-01-01 00:00:00.000001","1998-01-01 00:00:00","1997-12-31 23:59:59.000002"); 341 insert into t1 values(3,NULL,NULL,NULL,NULL,NULL); 342 select date_add(a,INTERVAL 1 SECOND), date_add(b,INTERVAL 1 MINUTE), date_add(c,INTERVAL 1 HOUR), date_add(d,INTERVAL 1 MONTH), date_add(e,INTERVAL 1 QUARTER) from t1; 343 date_add(a,INTERVAL 1 SECOND) date_add(b,INTERVAL 1 MINUTE) date_add(c,INTERVAL 1 HOUR) date_add(d,INTERVAL 1 MONTH) date_add(e,INTERVAL 1 QUARTER) 344 1997-12-31 00:00:01 1997-12-31 00:01:00 1998-01-01 00:59:59 1998-01-31 23:59:59 1998-03-31 23:59:59.000002000 345 1998-01-01 00:00:01 1998-01-01 00:01:00 1998-01-01 01:00:00 1998-02-01 00:00:00 1998-03-31 23:59:59.000002000 346 null null null null null 347 select date_add(a,INTERVAL 1 YEAR), date_add(b,"1:1" MINUTE_SECOND), date_add(c,INTERVAL "1:1" HOUR_MINUTE), date_add(d,INTERVAL "1:1" DAY_HOUR), date_add(e,INTERVAL "1 1" YEAR_MONTH) from t1; 348 SQL parser error: You have an error in your SQL syntax; check the manual that corresponds to your MatrixOne server version for the right syntax to use. syntax error at line 1 column 66 near " MINUTE_SECOND), date_add(c,INTERVAL "1:1" HOUR_MINUTE), date_add(d,INTERVAL "1:1" DAY_HOUR), date_add(e,INTERVAL "1 1" YEAR_MONTH) from t1;"; 349 select date_add(a,"1:1:1" HOUR_SECOND), date_add(b,"1:1:1" HOUR_SECOND), date_add(c,INTERVAL "1 1:1" DAY_MINUTE), date_add(d,INTERVAL "1 1:1:1" DAY_SECOND), date_add(e,INTERVAL "1 1" YEAR_MONTH) from t1; 350 SQL parser error: You have an error in your SQL syntax; check the manual that corresponds to your MatrixOne server version for the right syntax to use. syntax error at line 1 column 37 near " HOUR_SECOND), date_add(b,"1:1:1" HOUR_SECOND), date_add(c,INTERVAL "1 1:1" DAY_MINUTE), date_add(d,INTERVAL "1 1:1:1" DAY_SECOND), date_add(e,INTERVAL "1 1" YEAR_MONTH) from t1;"; 351 select date_sub(a,INTERVAL 1 SECOND), date_sub(b,INTERVAL 1 MINUTE), date_sub(c,INTERVAL 1 HOUR), date_sub(d,INTERVAL 1 MONTH), date_sub(e,INTERVAL 1 QUARTER) from t1; 352 date_sub(a,INTERVAL 1 SECOND) date_sub(b,INTERVAL 1 MINUTE) date_sub(c,INTERVAL 1 HOUR) date_sub(d,INTERVAL 1 MONTH) date_sub(e,INTERVAL 1 QUARTER) 353 1997-12-30 23:59:59 1997-12-30 23:59:00 1997-12-31 22:59:59 1997-11-30 23:59:59 1997-09-30 23:59:59.000002000 354 1997-12-31 23:59:59 1997-12-31 23:59:00 1997-12-31 23:00:00 1997-12-01 00:00:00 1997-09-30 23:59:59.000002000 355 null null null null null 356 select date_sub(a,INTERVAL 1 YEAR), date_sub(b,INTERVAL "1:1" MINUTE_SECOND), date_sub(c,INTERVAL "1:1" HOUR_MINUTE), date_sub(d,INTERVAL "1:1" DAY_HOUR), date_sub(e,INTERVAL "1 1" YEAR_MONTH) from t1; 357 date_sub(a,INTERVAL 1 YEAR) date_sub(b,INTERVAL "1:1" MINUTE_SECOND) date_sub(c,INTERVAL "1:1" HOUR_MINUTE) date_sub(d,INTERVAL "1:1" DAY_HOUR) date_sub(e,INTERVAL "1 1" YEAR_MONTH) 358 1996-12-31 1997-12-30 23:58:59 1997-12-31 22:58:59 1997-12-30 22:59:59 1996-11-30 23:59:59.000002000 359 1997-01-01 1997-12-31 23:58:59 1997-12-31 22:59:00 1997-12-30 23:00:00 1996-11-30 23:59:59.000002000 360 null null null null null 361 select date_sub(a,INTERVAL "1:1:1" HOUR_SECOND), date_sub(b,INTERVAL "1:1:1" HOUR_SECOND), date_sub(c,INTERVAL "1 1:1" DAY_MINUTE), date_sub(d,INTERVAL "1 1:1:1" DAY_SECOND), date_sub(e,INTERVAL "1 1" YEAR_MONTH) from t1; 362 date_sub(a,INTERVAL "1:1:1" HOUR_SECOND) date_sub(b,INTERVAL "1:1:1" HOUR_SECOND) date_sub(c,INTERVAL "1 1:1" DAY_MINUTE) date_sub(d,INTERVAL "1 1:1:1" DAY_SECOND) date_sub(e,INTERVAL "1 1" YEAR_MONTH) 363 1997-12-30 22:58:59 1997-12-30 22:58:59 1997-12-30 22:58:59 1997-12-30 22:58:58 1996-11-30 23:59:59.000002000 364 1997-12-31 22:58:59 1997-12-31 22:58:59 1997-12-30 22:59:00 1997-12-30 22:58:59 1996-11-30 23:59:59.000002000 365 null null null null null 366 select a + INTERVAL 1 SECOND,b + INTERVAL 1 MINUTE,c + INTERVAL 1 HOUR from t1; 367 a + INTERVAL 1 SECOND b + INTERVAL 1 MINUTE c + INTERVAL 1 HOUR 368 1997-12-31 00:00:01 1997-12-31 00:01:00 1998-01-01 00:59:59 369 1998-01-01 00:00:01 1998-01-01 00:01:00 1998-01-01 01:00:00 370 null null null 371 select a - INTERVAL 1 SECOND,b - INTERVAL 1 MINUTE,c - INTERVAL 1 HOUR from t1; 372 a - INTERVAL 1 SECOND b - INTERVAL 1 MINUTE c - INTERVAL 1 HOUR 373 1997-12-30 23:59:59 1997-12-30 23:59:00 1997-12-31 22:59:59 374 1997-12-31 23:59:59 1997-12-31 23:59:00 1997-12-31 23:00:00 375 null null null 376 select i + INTERVAL 1 SECOND from t1; 377 invalid argument operator +, bad value [INT unexpected type: 54] 378 select month(date_sub("1998-01-01 00:00:00",INTERVAL 1 SECOND)); 379 month(date_sub("1998-01-01 00:00:00",INTERVAL 1 SECOND)) 380 12 381 select weekday(date_sub("1998-01-01 00:00:00",INTERVAL 1 MINUTE)); 382 weekday(date_sub("1998-01-01 00:00:00",INTERVAL 1 MINUTE)) 383 2 384 select date(date_sub("1998-01-01 00:00:00",INTERVAL 1 DAY)); 385 date(date_sub("1998-01-01 00:00:00",INTERVAL 1 DAY)) 386 1997-12-31 387 select dayofyear(date_sub("1998-01-01 00:00:00",INTERVAL 1 MONTH)); 388 dayofyear(date_sub("1998-01-01 00:00:00",INTERVAL 1 MONTH)) 389 335 390 select month(date_add("1997-12-31 23:59:59",INTERVAL "1:1" MINUTE_SECOND)); 391 month(date_add("1997-12-31 23:59:59",INTERVAL "1:1" MINUTE_SECOND)) 392 1 393 select weekday(date_add("1997-12-31 23:59:59",INTERVAL "1:1" HOUR_MINUTE)); 394 weekday(date_add("1997-12-31 23:59:59",INTERVAL "1:1" HOUR_MINUTE)) 395 3 396 select date(date_add("1997-12-31 23:59:59",INTERVAL "1 1" YEAR_MONTH)); 397 date(date_add("1997-12-31 23:59:59",INTERVAL "1 1" YEAR_MONTH)) 398 1999-01-31 399 select dayofyear(date_add("1997-12-31 23:59:59",INTERVAL "1:1:1" HOUR_SECOND)); 400 dayofyear(date_add("1997-12-31 23:59:59",INTERVAL "1:1:1" HOUR_SECOND)) 401 1 402 select date("1997-12-31 23:59:59" + INTERVAL 1 SECOND) + INTERVAL "1:1:1" HOUR_SECOND; 403 date("1997-12-31 23:59:59" + INTERVAL 1 SECOND) + INTERVAL "1:1:1" HOUR_SECOND 404 1998-01-01 01:01:01 405 SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) AS DECIMAL(20,6)); 406 invalid argument operator cast, bad value [DATETIME DECIMAL128] 407 SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) + INTERVAL 14 MICROSECOND AS DECIMAL(20,6)); 408 invalid argument operator cast, bad value [DATETIME DECIMAL128] 409 drop table if exists t1; 410 drop table if exists t2; 411 create table t1(i int,a datetime,b datetime,c datetime,d char(200),e varchar(50)); 412 create table t2(i int,a datetime,b datetime,c datetime,d char(200),e varchar(50)); 413 insert into t1 select 1,"1997-12-30" + INTERVAL 1 SECOND ,"1997-12-31 23:59:59" + INTERVAL 1 MINUTE,"1997-12-31 23:59:59.000002" + INTERVAL 1 HOUR,"1997-12-31 23:59:59" + INTERVAL 1 DAY,"1997-12-31 23:59:59.000002" + INTERVAL 1 MONTH; 414 insert into t1 select 2,"1997-12-30" + INTERVAL 1 YEAR,"1997-12-31 23:59:59" + INTERVAL "1:1" MINUTE_SECOND,"1997-12-31 23:59:59.000002" + INTERVAL "1:1" DAY_HOUR,"1997-12-31 23:59:59" + INTERVAL "1 1" YEAR_MONTH,"1997-12-31 23:59:59.000002" + INTERVAL "1:1:1" HOUR_SECOND; 415 insert into t1 select 3,"1997-12-30" + INTERVAL "1:1:1" HOUR_SECOND,"1997-12-31 23:59:59" + INTERVAL "1 1:1" DAY_MINUTE,"1997-12-31 23:59:59.000002" + INTERVAL "1 1:1:1" DAY_SECOND,"1997-12-31 23:59:59","1997-12-31 23:59:59.000002"; 416 insert into t1 select 4,"1997-12-30" + INTERVAL 1 SECOND ,"1997-12-31 23:59:59" + INTERVAL 1 MINUTE,"1997-12-31 23:59:59.000002" + INTERVAL 1 HOUR,"1997-12-31 23:59:59" + INTERVAL 1 DAY,"1997-12-31 23:59:59.000002" + INTERVAL 1 MONTH; 417 insert into t1 select 5,"1997-12-30" + INTERVAL 1 YEAR,"1997-12-31 23:59:59" + INTERVAL "1:1" MINUTE_SECOND,"1997-12-31 23:59:59.000002" + INTERVAL "1:1" DAY_HOUR,"1997-12-31 23:59:59" + INTERVAL "1 1" YEAR_MONTH,"1997-12-31 23:59:59.000002" + INTERVAL "1:1:1" HOUR_SECOND; 418 insert into t1 select 6,"1997-12-30" + INTERVAL "1:1:1" HOUR_SECOND,"1997-12-31 23:59:59" + INTERVAL "1 1:1" DAY_MINUTE,"1997-12-31 23:59:59.000002" + INTERVAL "1 1:1:1" DAY_SECOND,"1997-12-31 23:59:59","1997-12-31 23:59:59.000002"; 419 insert into t1 values(7,"1998-01-01","1998-01-01","1998-01-01 00:00:00.000001","1998-01-01 00:00:00","1997-12-31 23:59:59.000002"); 420 insert into t1 values(8,"1998-01-01","1998-01-01","1998-01-01 00:00:00.000001","1998-01-01 00:00:00","1997-12-31 23:59:59.000002"); 421 insert into t1 values(9,"1998-01-01","1998-01-01","1998-01-01 00:00:00.000001","1998-01-01 00:00:00","1997-12-31 23:59:59.000002"); 422 insert into t1 select 10,"2010-11-12" + interval 14 microsecond,"1998-01-01 00:00:00","1998-01-01 00:00:00.000001","1998-01-01 00:00:00","1997-12-31 23:59:59.000002"; 423 insert into t1 values(11,NULL,NULL,NULL,NULL,NULL); 424 insert into t2 select 1,"1997-12-30" + INTERVAL 1 SECOND ,"1997-12-31 23:59:59" + INTERVAL 1 MINUTE,"1997-12-31 23:59:59.000002" + INTERVAL 1 HOUR,"1997-12-31 23:59:59" + INTERVAL 1 DAY,"1997-12-31 23:59:59.000002" + INTERVAL 1 MONTH; 425 insert into t2 select 2,"1997-12-30" + INTERVAL 1 YEAR,"1997-12-31 23:59:59" + INTERVAL "1:1" MINUTE_SECOND,"1997-12-31 23:59:59.000002" + INTERVAL "1:1" DAY_HOUR,"1997-12-31 23:59:59" + INTERVAL "1 1" YEAR_MONTH,"1997-12-31 23:59:59.000002" + INTERVAL "1:1:1" HOUR_SECOND; 426 insert into t2 select 3,"1997-12-30" + INTERVAL "1:1:1" HOUR_SECOND,"1997-12-31 23:59:59" + INTERVAL "1 1:1" DAY_MINUTE,"1997-12-31 23:59:59.000002" + INTERVAL "1 1:1:1" DAY_SECOND,"1997-12-31 23:59:59","1997-12-31 23:59:59.000002"; 427 insert into t2 select 4,"1997-12-30" + INTERVAL 1 SECOND ,"1997-12-31 23:59:59" + INTERVAL 1 MINUTE,"1997-12-31 23:59:59.000002" + INTERVAL 1 HOUR,"1997-12-31 23:59:59" + INTERVAL 1 DAY,"1997-12-31 23:59:59.000002" + INTERVAL 1 MONTH; 428 insert into t2 select 5,"1997-12-30" + INTERVAL 1 YEAR,"1997-12-31 23:59:59" + INTERVAL "1:1" MINUTE_SECOND,"1997-12-31 23:59:59.000002" + INTERVAL "1:1" DAY_HOUR,"1997-12-31 23:59:59" + INTERVAL "1 1" YEAR_MONTH,"1997-12-31 23:59:59.000002" + INTERVAL "1:1:1" HOUR_SECOND; 429 insert into t2 select 6,"1997-12-30" + INTERVAL "1:1:1" HOUR_SECOND,"1997-12-31 23:59:59" + INTERVAL "1 1:1" DAY_MINUTE,"1997-12-31 23:59:59.000002" + INTERVAL "1 1:1:1" DAY_SECOND,"1997-12-31 23:59:59","1997-12-31 23:59:59.000002"; 430 insert into t2 values(7,"1998-01-01","1998-01-01 00:00:00","1998-01-01 00:00:00.000001","1998-01-01 00:00:00","1997-12-31 23:59:59.000002"); 431 insert into t2 values(8,"1998-01-01","1998-01-01 00:00:00","1998-01-01 00:00:00.000001","1998-01-01 00:00:00","1997-12-31 23:59:59.000002"); 432 insert into t2 values(9,"1998-01-01","1998-01-01 00:00:00","1998-01-01 00:00:00.000001","1998-01-01 00:00:00","1997-12-31 23:59:59.000002"); 433 insert into t2 values(10,NULL,NULL,NULL,NULL,NULL); 434 select * from t1 where a = "1997-12-29" + INTERVAL 1 DAY; 435 i a b c d e 436 1 1997-12-30 1998-01-01 1998-01-01 00:59:59 1998-01-01 23:59:59 1998-01-31 23:59:59.000002 437 3 1997-12-30 1998-01-02 1998-01-02 01:01:00 1997-12-31 23:59:59 1997-12-31 23:59:59.000002 438 4 1997-12-30 1998-01-01 1998-01-01 00:59:59 1998-01-01 23:59:59 1998-01-31 23:59:59.000002 439 6 1997-12-30 1998-01-02 1998-01-02 01:01:00 1997-12-31 23:59:59 1997-12-31 23:59:59.000002 440 select * from t1 where a > "1997-12-29" + INTERVAL 1 DAY; 441 i a b c d e 442 2 1998-12-30 1998-01-01 1998-01-02 00:59:59 1999-01-31 23:59:59 1998-01-01 01:01:00.000002 443 5 1998-12-30 1998-01-01 1998-01-02 00:59:59 1999-01-31 23:59:59 1998-01-01 01:01:00.000002 444 7 1998-01-01 1998-01-01 1998-01-01 00:00:00 1998-01-01 00:00:00 1997-12-31 23:59:59.000002 445 8 1998-01-01 1998-01-01 1998-01-01 00:00:00 1998-01-01 00:00:00 1997-12-31 23:59:59.000002 446 9 1998-01-01 1998-01-01 1998-01-01 00:00:00 1998-01-01 00:00:00 1997-12-31 23:59:59.000002 447 10 2010-11-12 1998-01-01 1998-01-01 00:00:00 1998-01-01 00:00:00 1997-12-31 23:59:59.000002 448 select * from t1 where (a + INTERVAL 1 DAY) > "1997-12-31"; 449 i a b c d e 450 2 1998-12-30 1998-01-01 1998-01-02 00:59:59 1999-01-31 23:59:59 1998-01-01 01:01:00.000002 451 5 1998-12-30 1998-01-01 1998-01-02 00:59:59 1999-01-31 23:59:59 1998-01-01 01:01:00.000002 452 7 1998-01-01 1998-01-01 1998-01-01 00:00:00 1998-01-01 00:00:00 1997-12-31 23:59:59.000002 453 8 1998-01-01 1998-01-01 1998-01-01 00:00:00 1998-01-01 00:00:00 1997-12-31 23:59:59.000002 454 9 1998-01-01 1998-01-01 1998-01-01 00:00:00 1998-01-01 00:00:00 1997-12-31 23:59:59.000002 455 10 2010-11-12 1998-01-01 1998-01-01 00:00:00 1998-01-01 00:00:00 1997-12-31 23:59:59.000002 456 select * from t1 where (a + INTERVAL 1 DAY) <> ("1997-12-30" + INTERVAL 1 DAY); 457 i a b c d e 458 2 1998-12-30 1998-01-01 1998-01-02 00:59:59 1999-01-31 23:59:59 1998-01-01 01:01:00.000002 459 5 1998-12-30 1998-01-01 1998-01-02 00:59:59 1999-01-31 23:59:59 1998-01-01 01:01:00.000002 460 7 1998-01-01 1998-01-01 1998-01-01 00:00:00 1998-01-01 00:00:00 1997-12-31 23:59:59.000002 461 8 1998-01-01 1998-01-01 1998-01-01 00:00:00 1998-01-01 00:00:00 1997-12-31 23:59:59.000002 462 9 1998-01-01 1998-01-01 1998-01-01 00:00:00 1998-01-01 00:00:00 1997-12-31 23:59:59.000002 463 10 2010-11-12 1998-01-01 1998-01-01 00:00:00 1998-01-01 00:00:00 1997-12-31 23:59:59.000002 464 select date_add(b,INTERVAL 1 DAY),date_add(c,INTERVAL 1 SECOND) from t1; 465 date_add(b,INTERVAL 1 DAY) date_add(c,INTERVAL 1 SECOND) 466 1998-01-02 00:00:59 1998-01-01 01:00:00 467 1998-01-02 00:01:00 1998-01-02 01:00:00 468 1998-01-03 01:00:59 1998-01-02 01:01:01 469 1998-01-02 00:00:59 1998-01-01 01:00:00 470 1998-01-02 00:01:00 1998-01-02 01:00:00 471 1998-01-03 01:00:59 1998-01-02 01:01:01 472 1998-01-02 00:00:00 1998-01-01 00:00:01 473 1998-01-02 00:00:00 1998-01-01 00:00:01 474 1998-01-02 00:00:00 1998-01-01 00:00:01 475 1998-01-02 00:00:00 1998-01-01 00:00:01 476 null null 477 select distinct(a) from t1 where c > "1998-01-01 00:59:59"; 478 a 479 1998-12-30 480 1997-12-30 481 select count(a),c + INTERVAL 1 DAY as c1 from t1 group by (c + INTERVAL 1 DAY) having c1 > "1998-01-01 00:59:59"; 482 SQL syntax error: column "c1" must appear in the GROUP BY clause or be used in an aggregate function 483 select i,c + INTERVAL 1 MINUTE from t1 where a - INTERVAL 1 SECOND > "1997-01-01 00:00:00.000001" order by c + INTERVAL 1 MINUTE DESC; 484 i c + INTERVAL 1 MINUTE 485 3 1998-01-02 01:02:00 486 6 1998-01-02 01:02:00 487 2 1998-01-02 01:00:59 488 5 1998-01-02 01:00:59 489 1 1998-01-01 01:00:59 490 4 1998-01-01 01:00:59 491 7 1998-01-01 00:01:00 492 8 1998-01-01 00:01:00 493 9 1998-01-01 00:01:00 494 10 1998-01-01 00:01:00 495 select i,c + INTERVAL 1 MINUTE from t1 where a - INTERVAL 1 SECOND > "1997-01-01 00:00:00.000001" order by c + INTERVAL 1 MINUTE ASC; 496 i c + INTERVAL 1 MINUTE 497 7 1998-01-01 00:01:00 498 8 1998-01-01 00:01:00 499 9 1998-01-01 00:01:00 500 10 1998-01-01 00:01:00 501 1 1998-01-01 01:00:59 502 4 1998-01-01 01:00:59 503 2 1998-01-02 01:00:59 504 5 1998-01-02 01:00:59 505 3 1998-01-02 01:02:00 506 6 1998-01-02 01:02:00 507 select t1.i,t2.i,t1.c + INTERVAL 1 MINUTE,t2.b + INTERVAL 1 YEAR from t1 join t2 where (t1.a + INTERVAL 1 DAY) = (t2.c -INTERVAL 1 DAY ); 508 i i t1.c + INTERVAL 1 MINUTE t2.b + INTERVAL 1 YEAR 509 select '2007-01-01' + interval i day from t2; 510 '2007-01-01' + interval i day 511 2007-01-02 00:00:00 512 2007-01-03 00:00:00 513 2007-01-04 00:00:00 514 2007-01-05 00:00:00 515 2007-01-06 00:00:00 516 2007-01-07 00:00:00 517 2007-01-08 00:00:00 518 2007-01-09 00:00:00 519 2007-01-10 00:00:00 520 2007-01-11 00:00:00 521 select b + interval i day from t2; 522 b + interval i day 523 1998-01-02 00:00:59 524 1998-01-03 00:01:00 525 1998-01-05 01:00:59 526 1998-01-05 00:00:59 527 1998-01-06 00:01:00 528 1998-01-08 01:00:59 529 1998-01-08 00:00:00 530 1998-01-09 00:00:00 531 1998-01-10 00:00:00 532 null 533 update t1 set c = c + INTERVAL 1 DAY where i > 6; 534 select * from t1 where i > 6; 535 i a b c d e 536 7 1998-01-01 00:00:00 1998-01-01 00:00:00 1998-01-02 00:00:00 1998-01-01 00:00:00 1997-12-31 23:59:59.000002 537 8 1998-01-01 00:00:00 1998-01-01 00:00:00 1998-01-02 00:00:00 1998-01-01 00:00:00 1997-12-31 23:59:59.000002 538 9 1998-01-01 00:00:00 1998-01-01 00:00:00 1998-01-02 00:00:00 1998-01-01 00:00:00 1997-12-31 23:59:59.000002 539 10 2010-11-12 00:00:00 1998-01-01 00:00:00 1998-01-02 00:00:00 1998-01-01 00:00:00 1997-12-31 23:59:59.000002 540 11 null null null null null 541 drop table if exists t1; 542 drop table if exists t2; 543 drop table if exists t1; 544 CREATE TABLE t1 ( datum DATE ); 545 INSERT INTO t1 VALUES ( "2000-1-1" ); 546 INSERT INTO t1 VALUES ( "2000-1-2" ); 547 INSERT INTO t1 VALUES ( "2000-1-3" ); 548 INSERT INTO t1 VALUES ( "2000-1-4" ); 549 INSERT INTO t1 VALUES ( "2000-1-5" ); 550 SELECT * FROM t1 WHERE datum BETWEEN cast("2000-1-2" as date) AND cast("2000-1-4" as date); 551 datum 552 2000-01-02 553 2000-01-03 554 2000-01-04 555 SELECT * FROM t1 WHERE datum BETWEEN cast("2000-1-2" as date) AND datum - INTERVAL 100 DAY; 556 datum 557 SELECT CAST('2006-09-26' AS DATE) + INTERVAL 1 DAY; 558 CAST('2006-09-26' AS DATE) + INTERVAL 1 DAY 559 2006-09-27 560 SELECT CAST('2006-09-26' AS DATE) + INTERVAL 1 MONTH; 561 CAST('2006-09-26' AS DATE) + INTERVAL 1 MONTH 562 2006-10-26 563 SELECT CAST('2006-09-26' AS DATE) + INTERVAL 1 YEAR; 564 CAST('2006-09-26' AS DATE) + INTERVAL 1 YEAR 565 2007-09-26 566 SELECT CAST('2006-09-26' AS DATE) + INTERVAL 1 WEEK; 567 CAST('2006-09-26' AS DATE) + INTERVAL 1 WEEK 568 2006-10-03 569 drop table if exists t1; 570 create table t1 (d date); 571 insert into t1 (d) select date_sub('2000-01-01', INTERVAL 2001 YEAR); 572 Data truncation: data out of range: data type datetime, 573 insert into t1 (d) select date_add('2000-01-01',interval 8000 year); 574 Data truncation: data out of range: data type datetime, 575 insert into t1 select date_add(NULL, INTERVAL 1 DAY); 576 insert into t1 select date_add('2000-01-04', INTERVAL NULL DAY); 577 insert into t1 (d) select date_sub('2000-01-01', INTERVAL 2001 YEAR); 578 Data truncation: data out of range: data type datetime, 579 insert into t1 (d) select date_add('2000-01-01',interval 8000 year); 580 Data truncation: data out of range: data type datetime, 581 insert into t1 select date_add(NULL, INTERVAL 1 DAY); 582 insert into t1 select date_add('2000-01-04', INTERVAL NULL DAY); 583 select * from t1; 584 d 585 null 586 null 587 null 588 null 589 drop table t1;