|
1
by brian
clean slate |
1 |
#
|
2 |
# Test of date format functions |
|
3 |
#
|
|
4 |
||
5 |
--disable_warnings
|
|
6 |
drop table if exists t1; |
|
7 |
--enable_warnings
|
|
8 |
||
9 |
#
|
|
10 |
# The following tests will work only when we at some point will enable |
|
11 |
# dynamic changing of formats |
|
12 |
#
|
|
13 |
||
14 |
# SET date_format='%d.%m.%Y'; |
|
15 |
# select CAST('01.01.2001' as DATE) as a; |
|
16 |
# SET datetime_format='%d.%m.%Y %H.%i.%s'; |
|
17 |
# select CAST('01.01.2001 05.12.06' as DATETIME) as a; |
|
18 |
# SET time_format='%H.%i.%s'; |
|
19 |
# select CAST('05.12.06' as TIME) as a; |
|
20 |
#
|
|
21 |
# SET datetime_format='%d.%m.%Y %h:%i:%s %p'; |
|
22 |
# select CAST('01.01.2001 05:12:06AM' as DATETIME) as a; |
|
23 |
# select CAST('01.01.2001 05:12:06 PM' as DATETIME) as a; |
|
24 |
#
|
|
25 |
# SET time_format='%h:%i:%s %p'; |
|
26 |
# select CAST('05:12:06 AM' as TIME) as a; |
|
27 |
# select CAST('05:12:06.1234PM' as TIME) as a; |
|
28 |
#
|
|
29 |
# SET time_format='%h.%i.%s %p'; |
|
30 |
# SET date_format='%d.%m.%y'; |
|
31 |
# SET datetime_format='%d.%m.%y %h.%i.%s %p'; |
|
32 |
# select CAST('12-12-06' as DATE) as a; |
|
33 |
#
|
|
34 |
# select adddate('01.01.97 11.59.59.000001 PM', 10); |
|
35 |
# select datediff('31.12.97 11.59:59.000001 PM','01.01.98'); |
|
36 |
# select weekofyear('31.11.97 11:59:59.000001 PM'); |
|
37 |
# select makedate(1997,1); |
|
38 |
# select addtime('31.12.97 11.59.59.999999 PM', '1 1.1.1.000002'); |
|
39 |
# select maketime(23,11,12); |
|
40 |
# select timediff('01.01.97 11:59:59.000001 PM','31.12.95 11:59:59.000002 PM'); |
|
41 |
#
|
|
42 |
# SET time_format='%i:%s:%H'; |
|
43 |
# select cast(str_to_date('15-01-2001 12:59:59', '%d-%m-%Y %H:%i:%S') as TIME); |
|
44 |
||
|
907.1.2
by Jay Pipes
Merging in old r902 temporal changes |
45 |
explain extended select makedate(1997,1), microsecond("1997-12-31 23:59:59.000001"); |
|
1
by brian
clean slate |
46 |
|
47 |
#
|
|
48 |
# Test of date_format() |
|
49 |
#
|
|
50 |
||
51 |
create table t1 (d date); |
|
52 |
insert into t1 values ('2004-07-14'),('2005-07-14'); |
|
53 |
select date_format(d,"%d") from t1 order by 1; |
|
54 |
drop table t1; |
|
55 |
||
|
873.1.1
by Jay Pipes
Fixes the Field_date class to not allow any invalid input at |
56 |
#
|
|
1
by brian
clean slate |
57 |
# Bug #14016 |
58 |
#
|
|
59 |
create table t1 (f1 datetime); |
|
60 |
insert into t1 (f1) values ("2005-01-01"); |
|
61 |
insert into t1 (f1) values ("2005-02-01"); |
|
62 |
select date_format(f1, "%m") as d1, date_format(f1, "%M") as d2 from t1 order by date_format(f1, "%M"); |
|
63 |
drop table t1; |
|
64 |
||
65 |
#
|
|
66 |
# Bug#11326 |
|
67 |
# TIME_FORMAT using "%r" returns wrong hour using 24:00:00 in TIME column |
|
68 |
#
|
|
69 |
# This tests that 24:00:00 does not return PM, when it should be AM. |
|
70 |
# Some other values are being tested same time. |
|
71 |
#
|
|
72 |
||
73 |
SELECT TIME_FORMAT("24:00:00", '%r'); |
|
74 |
SELECT TIME_FORMAT("00:00:00", '%r'); |
|
75 |
SELECT TIME_FORMAT("12:00:00", '%r'); |
|
76 |
SELECT TIME_FORMAT("15:00:00", '%r'); |
|
77 |
SELECT TIME_FORMAT("01:00:00", '%r'); |
|
78 |
SELECT TIME_FORMAT("25:00:00", '%r'); |
|
79 |
||
80 |
#
|
|
81 |
# Bug#11324 |
|
82 |
# TIME_FORMAT using "%l:%i" returns 36:00 with 24:00:00 in TIME column |
|
83 |
#
|
|
84 |
# This tests that 24:00:00 does not change to "36:00 AM". Testing |
|
85 |
# some other values same time. |
|
86 |
#
|
|
87 |
||
88 |
SELECT TIME_FORMAT("00:00:00", '%l %p'); |
|
89 |
SELECT TIME_FORMAT("01:00:00", '%l %p'); |
|
90 |
SELECT TIME_FORMAT("12:00:00", '%l %p'); |
|
91 |
SELECT TIME_FORMAT("23:00:00", '%l %p'); |
|
92 |
SELECT TIME_FORMAT("24:00:00", '%l %p'); |
|
93 |
SELECT TIME_FORMAT("25:00:00", '%l %p'); |
|
94 |
||
95 |
#
|
|
96 |
# Bug#20729: Bad date_format() call makes mysql server crash |
|
97 |
#
|
|
98 |
SELECT DATE_FORMAT('%Y-%m-%d %H:%i:%s', 1151414896); |
|
99 |
||
100 |
--echo "End of 4.1 tests" |