← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to mysql-test/r/func_sapdb.result

  • Committer: brian
  • Date: 2008-06-25 05:29:13 UTC
  • Revision ID: brian@localhost.localdomain-20080625052913-6upwo0jsrl4lnapl
clean slate

Show diffs side-by-side

added added

removed removed

Lines of Context:
mysql-test/r/func_sapdb.result
 
1
drop table if exists t1, test;
 
2
select extract(DAY_MICROSECOND FROM "1999-01-02 10:11:12.000123");
 
3
extract(DAY_MICROSECOND FROM "1999-01-02 10:11:12.000123")
 
4
2101112000123
 
5
select extract(HOUR_MICROSECOND FROM "1999-01-02 10:11:12.000123");
 
6
extract(HOUR_MICROSECOND FROM "1999-01-02 10:11:12.000123")
 
7
101112000123
 
8
select extract(MINUTE_MICROSECOND FROM "1999-01-02 10:11:12.000123");
 
9
extract(MINUTE_MICROSECOND FROM "1999-01-02 10:11:12.000123")
 
10
1112000123
 
11
select extract(SECOND_MICROSECOND FROM "1999-01-02 10:11:12.000123");
 
12
extract(SECOND_MICROSECOND FROM "1999-01-02 10:11:12.000123")
 
13
12000123
 
14
select extract(MICROSECOND FROM "1999-01-02 10:11:12.000123");
 
15
extract(MICROSECOND FROM "1999-01-02 10:11:12.000123")
 
16
123
 
17
select date_format("1997-12-31 23:59:59.000002", "%f");
 
18
date_format("1997-12-31 23:59:59.000002", "%f")
 
19
000002
 
20
select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000 99:99:99.999999" DAY_MICROSECOND);
 
21
date_add("1997-12-31 23:59:59.000002",INTERVAL "10000 99:99:99.999999" DAY_MICROSECOND)
 
22
2025-05-23 04:40:39.000001
 
23
select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000:99:99.999999" HOUR_MICROSECOND);
 
24
date_add("1997-12-31 23:59:59.000002",INTERVAL "10000:99:99.999999" HOUR_MICROSECOND)
 
25
1999-02-21 17:40:39.000001
 
26
select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000:99.999999" MINUTE_MICROSECOND);
 
27
date_add("1997-12-31 23:59:59.000002",INTERVAL "10000:99.999999" MINUTE_MICROSECOND)
 
28
1998-01-07 22:41:39.000001
 
29
select date_add("1997-12-31 23:59:59.000002",INTERVAL "10000.999999" SECOND_MICROSECOND);
 
30
date_add("1997-12-31 23:59:59.000002",INTERVAL "10000.999999" SECOND_MICROSECOND)
 
31
1998-01-01 02:46:40.000001
 
32
select date_add("1997-12-31 23:59:59.000002",INTERVAL "999999" MICROSECOND);
 
33
date_add("1997-12-31 23:59:59.000002",INTERVAL "999999" MICROSECOND)
 
34
1998-01-01 00:00:00.000001
 
35
select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1 1:1:1.000002" DAY_MICROSECOND);
 
36
date_sub("1998-01-01 00:00:00.000001",INTERVAL "1 1:1:1.000002" DAY_MICROSECOND)
 
37
1997-12-30 22:58:58.999999
 
38
select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1:1.000002" HOUR_MICROSECOND);
 
39
date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1:1.000002" HOUR_MICROSECOND)
 
40
1997-12-31 22:58:58.999999
 
41
select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1.000002" MINUTE_MICROSECOND);
 
42
date_sub("1998-01-01 00:00:00.000001",INTERVAL "1:1.000002" MINUTE_MICROSECOND)
 
43
1997-12-31 23:58:58.999999
 
44
select date_sub("1998-01-01 00:00:00.000001",INTERVAL "1.000002" SECOND_MICROSECOND);
 
45
date_sub("1998-01-01 00:00:00.000001",INTERVAL "1.000002" SECOND_MICROSECOND)
 
46
1997-12-31 23:59:58.999999
 
47
select date_sub("1998-01-01 00:00:00.000001",INTERVAL "000002" MICROSECOND);
 
48
date_sub("1998-01-01 00:00:00.000001",INTERVAL "000002" MICROSECOND)
 
49
1997-12-31 23:59:59.999999
 
50
select adddate("1997-12-31 23:59:59.000001", 10);
 
51
adddate("1997-12-31 23:59:59.000001", 10)
 
52
1998-01-10 23:59:59.000001
 
53
select subdate("1997-12-31 23:59:59.000001", 10);
 
54
subdate("1997-12-31 23:59:59.000001", 10)
 
55
1997-12-21 23:59:59.000001
 
56
select datediff("1997-12-31 23:59:59.000001","1997-12-30");
 
57
datediff("1997-12-31 23:59:59.000001","1997-12-30")
 
58
1
 
59
select datediff("1997-11-30 23:59:59.000001","1997-12-31");
 
60
datediff("1997-11-30 23:59:59.000001","1997-12-31")
 
61
-31
 
62
SET @@SQL_MODE="ALLOW_INVALID_DATES";
 
63
select datediff("1997-11-31 23:59:59.000001","1997-12-31");
 
64
datediff("1997-11-31 23:59:59.000001","1997-12-31")
 
65
-30
 
66
SET @@SQL_MODE="";
 
67
select datediff("1997-11-31 23:59:59.000001","1997-12-31");
 
68
datediff("1997-11-31 23:59:59.000001","1997-12-31")
 
69
NULL
 
70
Warnings:
 
71
Warning 1292    Incorrect datetime value: '1997-11-31 23:59:59.000001'
 
72
select datediff("1997-11-30 23:59:59.000001",null);
 
73
datediff("1997-11-30 23:59:59.000001",null)
 
74
NULL
 
75
select weekofyear("1997-11-30 23:59:59.000001");
 
76
weekofyear("1997-11-30 23:59:59.000001")
 
77
48
 
78
select makedate(03,1);
 
79
makedate(03,1)
 
80
2003-01-01
 
81
select makedate('0003',1);
 
82
makedate('0003',1)
 
83
2003-01-01
 
84
select makedate(1997,1);
 
85
makedate(1997,1)
 
86
1997-01-01
 
87
select makedate(1997,0);
 
88
makedate(1997,0)
 
89
NULL
 
90
select makedate(9999,365);
 
91
makedate(9999,365)
 
92
9999-12-31
 
93
select makedate(9999,366);
 
94
makedate(9999,366)
 
95
NULL
 
96
select makedate(100,1);
 
97
makedate(100,1)
 
98
0100-01-01
 
99
select addtime("1997-12-31 23:59:59.999999", "1 1:1:1.000002");
 
100
addtime("1997-12-31 23:59:59.999999", "1 1:1:1.000002")
 
101
1998-01-02 01:01:01.000001
 
102
select subtime("1997-12-31 23:59:59.000001", "1 1:1:1.000002");
 
103
subtime("1997-12-31 23:59:59.000001", "1 1:1:1.000002")
 
104
1997-12-30 22:58:57.999999
 
105
select addtime("1997-12-31 23:59:59.999999", "1998-01-01 01:01:01.999999");
 
106
addtime("1997-12-31 23:59:59.999999", "1998-01-01 01:01:01.999999")
 
107
NULL
 
108
select subtime("1997-12-31 23:59:59.999999", "1998-01-01 01:01:01.999999");
 
109
subtime("1997-12-31 23:59:59.999999", "1998-01-01 01:01:01.999999")
 
110
NULL
 
111
select subtime("01:00:00.999999", "02:00:00.999998");
 
112
subtime("01:00:00.999999", "02:00:00.999998")
 
113
-00:59:59.999999
 
114
select subtime("02:01:01.999999", "01:01:01.999999");
 
115
subtime("02:01:01.999999", "01:01:01.999999")
 
116
01:00:00.000000
 
117
select timediff("1997-01-01 23:59:59.000001","1995-12-31 23:59:59.000002");
 
118
timediff("1997-01-01 23:59:59.000001","1995-12-31 23:59:59.000002")
 
119
838:59:59
 
120
Warnings:
 
121
Warning 1292    Truncated incorrect time value: '8807:59:59.999999'
 
122
select timediff("1997-12-31 23:59:59.000001","1997-12-30 01:01:01.000002");
 
123
timediff("1997-12-31 23:59:59.000001","1997-12-30 01:01:01.000002")
 
124
46:58:57.999999
 
125
select timediff("1997-12-30 23:59:59.000001","1997-12-31 23:59:59.000002");
 
126
timediff("1997-12-30 23:59:59.000001","1997-12-31 23:59:59.000002")
 
127
-24:00:00.000001
 
128
select timediff("1997-12-31 23:59:59.000001","23:59:59.000001");
 
129
timediff("1997-12-31 23:59:59.000001","23:59:59.000001")
 
130
NULL
 
131
select timediff("2000:01:01 00:00:00", "2000:01:01 00:00:00.000001");
 
132
timediff("2000:01:01 00:00:00", "2000:01:01 00:00:00.000001")
 
133
-00:00:00.000001
 
134
select timediff("2005-01-11 15:48:49.999999", "2005-01-11 15:48:50");
 
135
timediff("2005-01-11 15:48:49.999999", "2005-01-11 15:48:50")
 
136
-00:00:00.000001
 
137
select maketime(10,11,12);
 
138
maketime(10,11,12)
 
139
10:11:12
 
140
select maketime(25,11,12);
 
141
maketime(25,11,12)
 
142
25:11:12
 
143
select maketime(-25,11,12);
 
144
maketime(-25,11,12)
 
145
-25:11:12
 
146
select timestamp("2001-12-01", "01:01:01.999999");
 
147
timestamp("2001-12-01", "01:01:01.999999")
 
148
2001-12-01 01:01:01.999999
 
149
select timestamp("2001-13-01", "01:01:01.000001");
 
150
timestamp("2001-13-01", "01:01:01.000001")
 
151
NULL
 
152
Warnings:
 
153
Warning 1292    Incorrect datetime value: '2001-13-01'
 
154
select timestamp("2001-12-01", "25:01:01");
 
155
timestamp("2001-12-01", "25:01:01")
 
156
2001-12-02 01:01:01
 
157
select timestamp("2001-12-01 01:01:01.000100");
 
158
timestamp("2001-12-01 01:01:01.000100")
 
159
2001-12-01 01:01:01.000100
 
160
select timestamp("2001-12-01");
 
161
timestamp("2001-12-01")
 
162
2001-12-01 00:00:00
 
163
select day("1997-12-31 23:59:59.000001");
 
164
day("1997-12-31 23:59:59.000001")
 
165
31
 
166
select date("1997-12-31 23:59:59.000001");
 
167
date("1997-12-31 23:59:59.000001")
 
168
1997-12-31
 
169
select date("1997-13-31 23:59:59.000001");
 
170
date("1997-13-31 23:59:59.000001")
 
171
NULL
 
172
Warnings:
 
173
Warning 1292    Incorrect datetime value: '1997-13-31 23:59:59.000001'
 
174
select time("1997-12-31 23:59:59.000001");
 
175
time("1997-12-31 23:59:59.000001")
 
176
23:59:59.000001
 
177
select time("1997-12-31 25:59:59.000001");
 
178
time("1997-12-31 25:59:59.000001")
 
179
NULL
 
180
Warnings:
 
181
Warning 1292    Truncated incorrect time value: '1997-12-31 25:59:59.000001'
 
182
select microsecond("1997-12-31 23:59:59.000001");
 
183
microsecond("1997-12-31 23:59:59.000001")
 
184
1
 
185
create table t1 
 
186
select makedate(1997,1) as f1,
 
187
addtime(cast("1997-12-31 23:59:59.000001" as datetime), "1 1:1:1.000002") as f2,
 
188
addtime(cast("23:59:59.999999" as time) , "1 1:1:1.000002") as f3,
 
189
timediff("1997-12-31 23:59:59.000001","1997-12-30 01:01:01.000002") as f4,
 
190
timediff("1997-12-30 23:59:59.000001","1997-12-31 23:59:59.000002") as f5,
 
191
maketime(10,11,12) as f6,
 
192
timestamp(cast("2001-12-01" as date), "01:01:01") as f7,
 
193
date("1997-12-31 23:59:59.000001") as f8,
 
194
time("1997-12-31 23:59:59.000001") as f9;
 
195
describe t1;
 
196
Field   Type    Null    Key     Default Extra
 
197
f1      date    NO              0000-00-00      
 
198
f2      datetime        YES             NULL    
 
199
f3      time    YES             NULL    
 
200
f4      time    YES             NULL    
 
201
f5      time    YES             NULL    
 
202
f6      time    YES             NULL    
 
203
f7      datetime        YES             NULL    
 
204
f8      date    YES             NULL    
 
205
f9      time    YES             NULL    
 
206
select * from t1;
 
207
f1      f2      f3      f4      f5      f6      f7      f8      f9
 
208
1997-01-01      1998-01-02 01:01:00     49:01:01        46:58:57        -24:00:00       10:11:12        2001-12-01 01:01:01     1997-12-31      23:59:59
 
209
create table test(t1 datetime, t2 time, t3 time, t4 datetime);
 
210
insert into test values 
 
211
('2001-01-01 01:01:01', '01:01:01', null, '2001-02-01 01:01:01'),
 
212
('2001-01-01 01:01:01', '-01:01:01', '-23:59:59', "1997-12-31 23:59:59.000001"),
 
213
('1997-12-31 23:59:59.000001', '-23:59:59', '-01:01:01', '2001-01-01 01:01:01'),
 
214
('2001-01-01 01:01:01', '01:01:01', '-1 01:01:01', null),
 
215
('2001-01-01 01:01:01', '-01:01:01', '1 01:01:01', '2001-01-01 01:01:01'),
 
216
('2001-01-01 01:01:01', null, '-1 01:01:01', null),
 
217
(null, null, null, null),
 
218
('2001-01-01 01:01:01', '01:01:01', '1 01:01:01', '2001-01-01 01:01:01');
 
219
SELECT ADDTIME(t1,t2) As ttt, ADDTIME(t2, t3) As qqq from test;
 
220
ttt     qqq
 
221
2001-01-01 02:02:02     NULL
 
222
2001-01-01 00:00:00     -25:01:00
 
223
1997-12-31 00:00:00     -25:01:00
 
224
2001-01-01 02:02:02     -24:00:00
 
225
2001-01-01 00:00:00     24:00:00
 
226
NULL    NULL
 
227
NULL    NULL
 
228
2001-01-01 02:02:02     26:02:02
 
229
SELECT TIMEDIFF(t1, t4) As ttt, TIMEDIFF(t2, t3) As qqq,
 
230
TIMEDIFF(t3, t2) As eee, TIMEDIFF(t2, t4) As rrr from test;
 
231
ttt     qqq     eee     rrr
 
232
-744:00:00      NULL    NULL    NULL
 
233
838:59:59       22:58:58        -22:58:58       NULL
 
234
-838:59:59      -22:58:58       22:58:58        NULL
 
235
NULL    26:02:02        -26:02:02       NULL
 
236
00:00:00        -26:02:02       26:02:02        NULL
 
237
NULL    NULL    NULL    NULL
 
238
NULL    NULL    NULL    NULL
 
239
00:00:00        -24:00:00       24:00:00        NULL
 
240
Warnings:
 
241
Warning 1292    Truncated incorrect time value: '26305:01:02'
 
242
Warning 1292    Truncated incorrect time value: '-26305:01:02'
 
243
drop table t1, test;
 
244
select addtime("-01:01:01.01", "-23:59:59.1") as a;
 
245
a
 
246
-25:01:00.110000
 
247
select microsecond("1997-12-31 23:59:59.01") as a;
 
248
a
 
249
10000
 
250
select microsecond(19971231235959.01) as a;
 
251
a
 
252
10000
 
253
select date_add("1997-12-31",INTERVAL "10.09" SECOND_MICROSECOND) as a;
 
254
a
 
255
1997-12-31 00:00:10.090000
 
256
select str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S.%f");
 
257
str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S.%f")
 
258
2003-01-02 10:11:12.001200