summaryrefslogtreecommitdiff
path: root/mysql-test/r/date_formats.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r/date_formats.result')
-rw-r--r--mysql-test/r/date_formats.result137
1 files changed, 96 insertions, 41 deletions
diff --git a/mysql-test/r/date_formats.result b/mysql-test/r/date_formats.result
index 8439c40fbf9..23b7539a4f3 100644
--- a/mysql-test/r/date_formats.result
+++ b/mysql-test/r/date_formats.result
@@ -69,12 +69,12 @@ date format str_to_date
2003-01-02 02:11:12.12345AM %Y-%m-%d %h:%i:%S.%f %p 2003-01-02 02:11:12.123450
2003-01-02 12:11:12.12345 am %Y-%m-%d %h:%i:%S.%f%p 2003-01-02 00:11:12.123450
2003-01-02 11:11:12Pm %Y-%m-%d %h:%i:%S%p 2003-01-02 23:11:12.000000
-10:20:10 %H:%i:%s 0000-00-00 10:20:10.000000
-10:20:10 %h:%i:%s.%f 0000-00-00 10:20:10.000000
-10:20:10 %T 0000-00-00 10:20:10.000000
-10:20:10AM %h:%i:%s%p 0000-00-00 10:20:10.000000
-10:20:10AM %r 0000-00-00 10:20:10.000000
-10:20:10.44AM %h:%i:%s.%f%p 0000-00-00 10:20:10.440000
+10:20:10 %H:%i:%s NULL
+10:20:10 %h:%i:%s.%f NULL
+10:20:10 %T NULL
+10:20:10AM %h:%i:%s%p NULL
+10:20:10AM %r NULL
+10:20:10.44AM %h:%i:%s.%f%p NULL
15-01-2001 12:59:58 %d-%m-%Y %H:%i:%S 2001-01-15 12:59:58.000000
15 September 2001 %d %M %Y 2001-09-15 00:00:00.000000
15 SEPTEMB 2001 %d %M %Y 2001-09-15 00:00:00.000000
@@ -91,6 +91,13 @@ Tuesday 52 2001 %W %V %X 2002-01-01 00:00:00.000000
15-01-2001 %d-%m-%Y %H:%i:%S 2001-01-15 00:00:00.000000
15-01-20 %d-%m-%y 2020-01-15 00:00:00.000000
15-2001-1 %d-%Y-%c 2001-01-15 00:00:00.000000
+Warnings:
+Warning 1411 Incorrect datetime value: '10:20:10' for function str_to_date
+Warning 1411 Incorrect datetime value: '10:20:10' for function str_to_date
+Warning 1411 Incorrect datetime value: '10:20:10' for function str_to_date
+Warning 1411 Incorrect datetime value: '10:20:10AM' for function str_to_date
+Warning 1411 Incorrect datetime value: '10:20:10AM' for function str_to_date
+Warning 1411 Incorrect datetime value: '10:20:10.44AM' for function str_to_date
select date,format,concat('',str_to_date(date, format)) as con from t1;
date format con
2003-01-02 10:11:12 %Y-%m-%d %H:%i:%S 2003-01-02 10:11:12.000000
@@ -102,12 +109,12 @@ date format con
2003-01-02 02:11:12.12345AM %Y-%m-%d %h:%i:%S.%f %p 2003-01-02 02:11:12.123450
2003-01-02 12:11:12.12345 am %Y-%m-%d %h:%i:%S.%f%p 2003-01-02 00:11:12.123450
2003-01-02 11:11:12Pm %Y-%m-%d %h:%i:%S%p 2003-01-02 23:11:12.000000
-10:20:10 %H:%i:%s 0000-00-00 10:20:10.000000
-10:20:10 %h:%i:%s.%f 0000-00-00 10:20:10.000000
-10:20:10 %T 0000-00-00 10:20:10.000000
-10:20:10AM %h:%i:%s%p 0000-00-00 10:20:10.000000
-10:20:10AM %r 0000-00-00 10:20:10.000000
-10:20:10.44AM %h:%i:%s.%f%p 0000-00-00 10:20:10.440000
+10:20:10 %H:%i:%s NULL
+10:20:10 %h:%i:%s.%f NULL
+10:20:10 %T NULL
+10:20:10AM %h:%i:%s%p NULL
+10:20:10AM %r NULL
+10:20:10.44AM %h:%i:%s.%f%p NULL
15-01-2001 12:59:58 %d-%m-%Y %H:%i:%S 2001-01-15 12:59:58.000000
15 September 2001 %d %M %Y 2001-09-15 00:00:00.000000
15 SEPTEMB 2001 %d %M %Y 2001-09-15 00:00:00.000000
@@ -124,6 +131,13 @@ Tuesday 52 2001 %W %V %X 2002-01-01 00:00:00.000000
15-01-2001 %d-%m-%Y %H:%i:%S 2001-01-15 00:00:00.000000
15-01-20 %d-%m-%y 2020-01-15 00:00:00.000000
15-2001-1 %d-%Y-%c 2001-01-15 00:00:00.000000
+Warnings:
+Warning 1411 Incorrect datetime value: '10:20:10' for function str_to_date
+Warning 1411 Incorrect datetime value: '10:20:10' for function str_to_date
+Warning 1411 Incorrect datetime value: '10:20:10' for function str_to_date
+Warning 1411 Incorrect datetime value: '10:20:10AM' for function str_to_date
+Warning 1411 Incorrect datetime value: '10:20:10AM' for function str_to_date
+Warning 1411 Incorrect datetime value: '10:20:10.44AM' for function str_to_date
select date,format,cast(str_to_date(date, format) as datetime) as datetime from t1;
date format datetime
2003-01-02 10:11:12 %Y-%m-%d %H:%i:%S 2003-01-02 10:11:12
@@ -135,12 +149,12 @@ date format datetime
2003-01-02 02:11:12.12345AM %Y-%m-%d %h:%i:%S.%f %p 2003-01-02 02:11:12
2003-01-02 12:11:12.12345 am %Y-%m-%d %h:%i:%S.%f%p 2003-01-02 00:11:12
2003-01-02 11:11:12Pm %Y-%m-%d %h:%i:%S%p 2003-01-02 23:11:12
-10:20:10 %H:%i:%s 0000-00-00 10:20:10
-10:20:10 %h:%i:%s.%f 0000-00-00 10:20:10
-10:20:10 %T 0000-00-00 10:20:10
-10:20:10AM %h:%i:%s%p 0000-00-00 10:20:10
-10:20:10AM %r 0000-00-00 10:20:10
-10:20:10.44AM %h:%i:%s.%f%p 0000-00-00 10:20:10
+10:20:10 %H:%i:%s NULL
+10:20:10 %h:%i:%s.%f NULL
+10:20:10 %T NULL
+10:20:10AM %h:%i:%s%p NULL
+10:20:10AM %r NULL
+10:20:10.44AM %h:%i:%s.%f%p NULL
15-01-2001 12:59:58 %d-%m-%Y %H:%i:%S 2001-01-15 12:59:58
15 September 2001 %d %M %Y 2001-09-15 00:00:00
15 SEPTEMB 2001 %d %M %Y 2001-09-15 00:00:00
@@ -157,6 +171,13 @@ Tuesday 52 2001 %W %V %X 2002-01-01 00:00:00
15-01-2001 %d-%m-%Y %H:%i:%S 2001-01-15 00:00:00
15-01-20 %d-%m-%y 2020-01-15 00:00:00
15-2001-1 %d-%Y-%c 2001-01-15 00:00:00
+Warnings:
+Warning 1411 Incorrect datetime value: '10:20:10' for function str_to_date
+Warning 1411 Incorrect datetime value: '10:20:10' for function str_to_date
+Warning 1411 Incorrect datetime value: '10:20:10' for function str_to_date
+Warning 1411 Incorrect datetime value: '10:20:10AM' for function str_to_date
+Warning 1411 Incorrect datetime value: '10:20:10AM' for function str_to_date
+Warning 1411 Incorrect datetime value: '10:20:10.44AM' for function str_to_date
select date,format,DATE(str_to_date(date, format)) as date2 from t1;
date format date2
2003-01-02 10:11:12 %Y-%m-%d %H:%i:%S 2003-01-02
@@ -168,12 +189,12 @@ date format date2
2003-01-02 02:11:12.12345AM %Y-%m-%d %h:%i:%S.%f %p 2003-01-02
2003-01-02 12:11:12.12345 am %Y-%m-%d %h:%i:%S.%f%p 2003-01-02
2003-01-02 11:11:12Pm %Y-%m-%d %h:%i:%S%p 2003-01-02
-10:20:10 %H:%i:%s 0000-00-00
-10:20:10 %h:%i:%s.%f 0000-00-00
-10:20:10 %T 0000-00-00
-10:20:10AM %h:%i:%s%p 0000-00-00
-10:20:10AM %r 0000-00-00
-10:20:10.44AM %h:%i:%s.%f%p 0000-00-00
+10:20:10 %H:%i:%s NULL
+10:20:10 %h:%i:%s.%f NULL
+10:20:10 %T NULL
+10:20:10AM %h:%i:%s%p NULL
+10:20:10AM %r NULL
+10:20:10.44AM %h:%i:%s.%f%p NULL
15-01-2001 12:59:58 %d-%m-%Y %H:%i:%S 2001-01-15
15 September 2001 %d %M %Y 2001-09-15
15 SEPTEMB 2001 %d %M %Y 2001-09-15
@@ -190,6 +211,13 @@ Tuesday 52 2001 %W %V %X 2002-01-01
15-01-2001 %d-%m-%Y %H:%i:%S 2001-01-15
15-01-20 %d-%m-%y 2020-01-15
15-2001-1 %d-%Y-%c 2001-01-15
+Warnings:
+Warning 1411 Incorrect datetime value: '10:20:10' for function str_to_date
+Warning 1411 Incorrect datetime value: '10:20:10' for function str_to_date
+Warning 1411 Incorrect datetime value: '10:20:10' for function str_to_date
+Warning 1411 Incorrect datetime value: '10:20:10AM' for function str_to_date
+Warning 1411 Incorrect datetime value: '10:20:10AM' for function str_to_date
+Warning 1411 Incorrect datetime value: '10:20:10.44AM' for function str_to_date
select date,format,TIME(str_to_date(date, format)) as time from t1;
date format time
2003-01-02 10:11:12 %Y-%m-%d %H:%i:%S 10:11:12.000000
@@ -201,12 +229,12 @@ date format time
2003-01-02 02:11:12.12345AM %Y-%m-%d %h:%i:%S.%f %p 02:11:12.123450
2003-01-02 12:11:12.12345 am %Y-%m-%d %h:%i:%S.%f%p 00:11:12.123450
2003-01-02 11:11:12Pm %Y-%m-%d %h:%i:%S%p 23:11:12.000000
-10:20:10 %H:%i:%s 10:20:10.000000
-10:20:10 %h:%i:%s.%f 10:20:10.000000
-10:20:10 %T 10:20:10.000000
-10:20:10AM %h:%i:%s%p 10:20:10.000000
-10:20:10AM %r 10:20:10.000000
-10:20:10.44AM %h:%i:%s.%f%p 10:20:10.440000
+10:20:10 %H:%i:%s NULL
+10:20:10 %h:%i:%s.%f NULL
+10:20:10 %T NULL
+10:20:10AM %h:%i:%s%p NULL
+10:20:10AM %r NULL
+10:20:10.44AM %h:%i:%s.%f%p NULL
15-01-2001 12:59:58 %d-%m-%Y %H:%i:%S 12:59:58.000000
15 September 2001 %d %M %Y 00:00:00.000000
15 SEPTEMB 2001 %d %M %Y 00:00:00.000000
@@ -223,6 +251,13 @@ Tuesday 52 2001 %W %V %X 00:00:00.000000
15-01-2001 %d-%m-%Y %H:%i:%S 00:00:00.000000
15-01-20 %d-%m-%y 00:00:00.000000
15-2001-1 %d-%Y-%c 00:00:00.000000
+Warnings:
+Warning 1411 Incorrect datetime value: '10:20:10' for function str_to_date
+Warning 1411 Incorrect datetime value: '10:20:10' for function str_to_date
+Warning 1411 Incorrect datetime value: '10:20:10' for function str_to_date
+Warning 1411 Incorrect datetime value: '10:20:10AM' for function str_to_date
+Warning 1411 Incorrect datetime value: '10:20:10AM' for function str_to_date
+Warning 1411 Incorrect datetime value: '10:20:10.44AM' for function str_to_date
select date,format,concat(TIME(str_to_date(date, format))) as time2 from t1;
date format time2
2003-01-02 10:11:12 %Y-%m-%d %H:%i:%S 10:11:12.000000
@@ -234,12 +269,12 @@ date format time2
2003-01-02 02:11:12.12345AM %Y-%m-%d %h:%i:%S.%f %p 02:11:12.123450
2003-01-02 12:11:12.12345 am %Y-%m-%d %h:%i:%S.%f%p 00:11:12.123450
2003-01-02 11:11:12Pm %Y-%m-%d %h:%i:%S%p 23:11:12.000000
-10:20:10 %H:%i:%s 10:20:10.000000
-10:20:10 %h:%i:%s.%f 10:20:10.000000
-10:20:10 %T 10:20:10.000000
-10:20:10AM %h:%i:%s%p 10:20:10.000000
-10:20:10AM %r 10:20:10.000000
-10:20:10.44AM %h:%i:%s.%f%p 10:20:10.440000
+10:20:10 %H:%i:%s NULL
+10:20:10 %h:%i:%s.%f NULL
+10:20:10 %T NULL
+10:20:10AM %h:%i:%s%p NULL
+10:20:10AM %r NULL
+10:20:10.44AM %h:%i:%s.%f%p NULL
15-01-2001 12:59:58 %d-%m-%Y %H:%i:%S 12:59:58.000000
15 September 2001 %d %M %Y 00:00:00.000000
15 SEPTEMB 2001 %d %M %Y 00:00:00.000000
@@ -256,6 +291,13 @@ Tuesday 52 2001 %W %V %X 00:00:00.000000
15-01-2001 %d-%m-%Y %H:%i:%S 00:00:00.000000
15-01-20 %d-%m-%y 00:00:00.000000
15-2001-1 %d-%Y-%c 00:00:00.000000
+Warnings:
+Warning 1411 Incorrect datetime value: '10:20:10' for function str_to_date
+Warning 1411 Incorrect datetime value: '10:20:10' for function str_to_date
+Warning 1411 Incorrect datetime value: '10:20:10' for function str_to_date
+Warning 1411 Incorrect datetime value: '10:20:10AM' for function str_to_date
+Warning 1411 Incorrect datetime value: '10:20:10AM' for function str_to_date
+Warning 1411 Incorrect datetime value: '10:20:10.44AM' for function str_to_date
select concat('',str_to_date('8:11:2.123456 03-01-02','%H:%i:%S.%f %y-%m-%d'));
concat('',str_to_date('8:11:2.123456 03-01-02','%H:%i:%S.%f %y-%m-%d'))
2003-01-02 08:11:02.123456
@@ -352,18 +394,18 @@ insert into t1 values
('03-01-02 10:11:12 PM', '%Y-%m-%d %h:%i:%S %p');
select date,format,str_to_date(date, format) as str_to_date from t1;
date format str_to_date
-10:20:10AM %h:%i:%s 0000-00-00 10:20:10.000000
+10:20:10AM %h:%i:%s NULL
2003-01-02 10:11:12 %Y-%m-%d %h:%i:%S 2003-01-02 10:11:12.000000
03-01-02 10:11:12 PM %Y-%m-%d %h:%i:%S %p 2003-01-02 22:11:12.000000
Warnings:
-Warning 1292 Truncated incorrect datetime value: '10:20:10AM'
+Warning 1411 Incorrect datetime value: '10:20:10AM' for function str_to_date
select date,format,concat(str_to_date(date, format),'') as con from t1;
date format con
-10:20:10AM %h:%i:%s 0000-00-00 10:20:10.000000
+10:20:10AM %h:%i:%s NULL
2003-01-02 10:11:12 %Y-%m-%d %h:%i:%S 2003-01-02 10:11:12.000000
03-01-02 10:11:12 PM %Y-%m-%d %h:%i:%S %p 2003-01-02 22:11:12.000000
Warnings:
-Warning 1292 Truncated incorrect datetime value: '10:20:10AM'
+Warning 1411 Incorrect datetime value: '10:20:10AM' for function str_to_date
drop table t1;
select get_format(DATE, 'USA') as a;
a
@@ -404,6 +446,13 @@ create table t1 select str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%
str_to_date("10:11:12.0012", "%H:%i:%S.%f") as f2,
str_to_date("2003-01-02", "%Y-%m-%d") as f3,
str_to_date("02", "%d") as f4, str_to_date("02 10", "%d %H") as f5;
+ERROR HY000: Incorrect datetime value: '02' for function str_to_date
+set sql_mode = 'NO_ENGINE_SUBSTITUTION';
+create table t1 select str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S.%f") as f1,
+str_to_date("10:11:12.0012", "%H:%i:%S.%f") as f2,
+str_to_date("2003-01-02", "%Y-%m-%d") as f3,
+str_to_date("02", "%d") as f4, str_to_date("02 10", "%d %H") as f5;
+set sql_mode = DEFAULT;
describe t1;
Field Type Null Key Default Extra
f1 datetime(6) YES NULL
@@ -418,8 +467,14 @@ drop table t1;
create table t1 select "02 10" as a, "%d %H" as b;
select str_to_date(a,b) from t1;
str_to_date(a,b)
-0000-00-02 10:00:00.000000
+NULL
+Warnings:
+Warning 1411 Incorrect datetime value: '02 10' for function str_to_date
+create table t2 select str_to_date(a,b) from t1;
+ERROR HY000: Incorrect datetime value: '02 10' for function str_to_date
+set sql_mode = 'NO_ENGINE_SUBSTITUTION';
create table t2 select str_to_date(a,b) from t1;
+set sql_mode = DEFAULT;
describe t2;
Field Type Null Key Default Extra
str_to_date(a,b) datetime(6) YES NULL
View Lorry Controller Status