Scrum task 835 - text-to-datetime conversion function

1 files changed, 82 insertions, 0 deletions

diff --git a/mysql-test/t/date_formats.test b/mysql-test/t/date_formats.test
new file mode 100644
index 00000000000..9551efaa648
--- /dev/null
+++ b/mysql-test/t/date_formats.test
@@ -0,0 +1,82 @@
+SHOW GLOBAL VARIABLES LIKE "%_format%";
+SHOW SESSION VARIABLES LIKE "%_format%";
+
+SET date_format="%d.%m.%Y";
+select CAST("01.01.2001" as DATE) as a;
+SET datetime_format="%d.%m.%Y %H.%i.%s";
+select CAST("01.01.2001 05.12.06" as DATETIME) as a;
+SET time_format="%H.%i.%s";
+select CAST("05.12.06" as TIME) as a;
+
+SET datetime_format="%d.%m.%Y %h:%i:%s %p";
+select CAST("01.01.2001 05:12:06AM" as DATETIME) as a;
+select CAST("01.01.2001 05:12:06 PM" as DATETIME) as a;
+
+SET time_format="%h:%i:%s %p";
+select CAST("05:12:06 AM" as TIME) as a;
+select CAST("05:12:06.1234PM" as TIME) as a;
+
+SET time_format="%h.%i.%s %p";
+SET date_format='%d.%m.%y';
+SET datetime_format="%d.%m.%y %h.%i.%s %p";
+select CAST("12-12-06" as DATE) as a;
+
+select adddate("01.01.97 11.59.59.000001 PM", 10);
+select datediff("31.12.97 11.59:59.000001 PM","01.01.98");
+select weekofyear("31.11.97 11:59:59.000001 PM");
+select makedate(1997,1);
+select addtime("31.12.97 11.59.59.999999 PM", "1 1.1.1.000002");
+select maketime(23,11,12);
+select timediff("01.01.97 11:59:59.000001 PM","31.12.95 11:59:59.000002 PM");
+
+SET time_format="%H%i%s";
+--error 1105
+SET time_format="%h%i%s";
+--error 1105
+SET date_format='%d.%m.%d';
+--error 1105
+SET datetime_format="%d.%m.%y %h.%i.%s";
+
+SET GLOBAL date_format=default;
+SHOW GLOBAL VARIABLES LIKE "date_format%";
+SET GLOBAL time_format=default;
+SHOW GLOBAL VARIABLES LIKE "time_format%";
+SET GLOBAL datetime_format=default;
+SHOW GLOBAL VARIABLES LIKE "datetime_format%";
+
+SET date_format=default;
+SHOW SESSION VARIABLES LIKE "date_format%";
+SET time_format=default;
+SHOW SESSION VARIABLES LIKE "time_format%";
+SET datetime_format=default;
+SHOW SESSION VARIABLES LIKE "datetime_format%";
+
+SET time_format='%i:%s:%H';
+select cast(str_to_date("15-01-2001 12:59:59", "%d-%m-%Y %H:%i:%S") as TIME);
+
+SET GLOBAL date_format='%Y-%m-%d';
+SET GLOBAL time_format='%H:%i:%s';
+SET GLOBAL datetime_format='%Y-%m-%d %H:%i:%s';
+SET date_format='%Y-%m-%d';
+SET time_format='%H:%i:%s';
+SET datetime_format='%Y-%m-%d %H:%i:%s';
+
+select str_to_date("15-01-2001 12:59:59", "%d-%m-%Y %H:%i:%S");
+select str_to_date("15 September 2001", "%d %M %Y");
+select str_to_date("15 Septembeb 2001", "%d %M %Y");
+select str_to_date("15 MAY 2001", "%d %b %Y");
+select str_to_date("Sunday 15 MAY 2001", "%W %d %b %Y");
+select str_to_date("Sundai 15 MAY 2001", "%W %d %b %Y");
+select str_to_date("Sundai 15 MA", "%W %d %b %Y");
+select str_to_date("Tuesday 52 2001", "%W %V %X");
+select str_to_date("Sunday 01 2001", "%W %V %X");
+select str_to_date("Tuesday 00 2002", "%W %U %Y");
+select str_to_date("Thursday 53 1998", "%W %u %Y");
+select str_to_date("15-01-2001", "%d-%m-%Y %H:%i:%S");
+select str_to_date("15-01-20", "%d-%m-%Y");
+select str_to_date("15-2001-1", "%d-%Y-%c");
+
+select get_format(DATE, 'USA') as a;
+select get_format(TIME, 'internal') as a;
+select get_format(DATETIME, 'eur') as a;
+