14
14
select date_format("1997-01-02", concat("%M %W %D ","%Y %y %m %d %h %i %s %w"));
15
15
select dayofmonth("1997-01-02"),dayofmonth(19970323);
16
16
select month("1997-01-02"),year("98-02-03"),dayofyear("1997-12-31");
17
--error 1686 # No more bad dates!
17
--error ER_INVALID_DATETIME_VALUE # No more bad dates!
18
18
select month("2001-02-00"),year("2001-01-01");
19
19
select DAYOFYEAR("1997-03-03"), QUARTER(980303);
20
20
select HOUR("1997-03-03 23:03:22"), MINUTE("23:03:22"), SECOND(230322);
92
92
select extract(DAY FROM "1999-01-02");
93
93
select extract(DAY_HOUR FROM "1999-01-02 10:11:12");
94
94
# The following is an interval, not a time!
95
--error 1686 # Bad datetime
95
--error ER_INVALID_DATETIME_VALUE # Bad datetime
96
96
select extract(DAY_MINUTE FROM "02 10:11:12");
97
97
# The following is an interval, not a time!
98
--error 1686 # Bad datetime
98
--error ER_INVALID_DATETIME_VALUE # Bad datetime
99
99
select extract(DAY_SECOND FROM "225 10:11:12");
100
100
select extract(HOUR FROM "1999-01-02 10:11:12");
101
101
select extract(HOUR_MINUTE FROM "10:11:12");
103
103
select extract(MINUTE FROM "10:11:12");
104
104
select extract(MINUTE_SECOND FROM "10:11:12");
105
105
select extract(SECOND FROM "1999-01-02 10:11:12");
106
--error 1686 # Bad datetime
106
--error ER_INVALID_DATETIME_VALUE # Bad datetime
107
107
select extract(MONTH FROM "2001-02-00");
161
161
CREATE TEMPORARY TABLE t1 (updated text) ENGINE=MyISAM;
162
162
INSERT INTO t1 VALUES ('');
163
--error 1686 # "" is not a date!
163
--error ER_INVALID_DATETIME_VALUE # "" is not a date!
164
164
SELECT month(updated) from t1;
165
--error 1686 # "" is not a date for pete's sake.
165
--error ER_INVALID_DATETIME_VALUE # "" is not a date for pete's sake.
166
166
SELECT year(updated) from t1;
174
174
create table t1 (d date, dt datetime, t timestamp, c char(10));
175
175
insert into t1 values (null, null, null, null);
176
--error 1686 # Bad dates...
176
--error ER_INVALID_DATETIME_VALUE # Bad dates...
177
177
insert into t1 values ("0000-00-00", "0000-00-00", "0000-00-00", "0000-00-00");
178
--error 1686 # 0000-00-00 is a bad date
178
--error ER_INVALID_DATETIME_VALUE # 0000-00-00 is a bad date
179
179
select dayofyear("0000-00-00"),dayofyear(d),dayofyear(dt),dayofyear(t),dayofyear(c) from t1;
180
--error 1686 # 0000-00-00 is a bad date
180
--error ER_INVALID_DATETIME_VALUE # 0000-00-00 is a bad date
181
181
select dayofmonth("0000-00-00"),dayofmonth(d),dayofmonth(dt),dayofmonth(t),dayofmonth(c) from t1;
182
--error 1686 # 0000-00-00 is not a damn date.
182
--error ER_INVALID_DATETIME_VALUE # 0000-00-00 is not a damn date.
183
183
select month("0000-00-00"),month(d),month(dt),month(t),month(c) from t1;
184
--error 1686 # 0000-00-00 not a date
184
--error ER_INVALID_DATETIME_VALUE # 0000-00-00 not a date
185
185
select quarter("0000-00-00"),quarter(d),quarter(dt),quarter(t),quarter(c) from t1;
186
--error 1686 # Argh. 0000-00-00 is not a date.
186
--error ER_INVALID_DATETIME_VALUE # Argh. 0000-00-00 is not a date.
187
187
select year("0000-00-00"),year(d),year(dt),year(t),year(c) from t1;
188
--error 1686 # Bad datetime
188
--error ER_INVALID_DATETIME_VALUE # Bad datetime
189
189
select to_days("0000-00-00"),to_days(d),to_days(dt),to_days(c) from t1;
190
--error 1686 # Ugh. bad datetime
190
--error ER_INVALID_DATETIME_VALUE # Ugh. bad datetime
191
191
select extract(MONTH FROM "0000-00-00"),extract(MONTH FROM d),extract(MONTH FROM dt),extract(MONTH FROM t),extract(MONTH FROM c) from t1;
243
243
# check for invalid dates
246
--error 1685 # Bad timestamp
246
--error ER_INVALID_UNIX_TIMESTAMP_VALUE # Bad timestamp
247
247
select unix_timestamp('2039-01-20 01:00:00');
248
--error 1685 # Bad timestamp
248
--error ER_INVALID_UNIX_TIMESTAMP_VALUE # Bad timestamp
249
249
select unix_timestamp('1968-01-20 01:00:00');
251
--error 1685 # Bad timestamp
251
--error ER_INVALID_UNIX_TIMESTAMP_VALUE # Bad timestamp
252
252
select unix_timestamp('2038-02-10 01:00:00');
253
--error 1685 # Bad timestamp
253
--error ER_INVALID_UNIX_TIMESTAMP_VALUE # Bad timestamp
254
254
select unix_timestamp('1969-11-20 01:00:00');
256
--error 1685 # Bad timestamp
256
--error ER_INVALID_UNIX_TIMESTAMP_VALUE # Bad timestamp
257
257
select unix_timestamp('2038-01-20 01:00:00');
258
--error 1685 # Bad timestamp
258
--error ER_INVALID_UNIX_TIMESTAMP_VALUE # Bad timestamp
259
259
select unix_timestamp('1969-12-30 01:00:00');
358
358
# test for last_day
359
359
select last_day('2000-02-05');
360
360
select last_day('2002-12-31');
361
--error 1686 # Bad date
361
--error ER_INVALID_DATETIME_VALUE # Bad date
362
362
select last_day('2003-03-32');
363
363
select last_day('2003-04-01');
364
364
select last_day('2001-01-01 01:01:01');
402
--error 1686 # Bad date
402
--error ER_INVALID_DATETIME_VALUE # Bad date
403
403
select last_day('2005-00-00');
404
--error 1686 # Bad date
404
--error ER_INVALID_DATETIME_VALUE # Bad date
405
405
select last_day('2005-00-01');
406
--error 1686 # Bad date
406
--error ER_INVALID_DATETIME_VALUE # Bad date
407
407
select last_day('2005-01-00');
498
498
INSERT INTO t2 VALUES
499
499
(1, '2005-08-01'), (2, '2005-06-15'), (3, '2005-07-15');
501
502
SELECT * FROM t1, t2
502
503
WHERE t1.day BETWEEN
503
504
'2005.09.01' - INTERVAL 6 MONTH AND t2.day;
504
506
SELECT * FROM t1, t2
505
507
WHERE CAST(t1.day AS DATE) BETWEEN
506
508
'2005.09.01' - INTERVAL 6 MONTH AND t2.day;
added
removed
tests/t/func_time.test
