Task #835: additional changes fot str_to_date

include/mysqld_error.h:
  Task #835: additional changes fot str_to_date
  New error message
mysql-test/r/date_formats.result:
  Task #835: additional changes fot str_to_date
  tests
mysql-test/r/func_sapdb.result:
  Task #835: additional changes fot str_to_date
  tests
mysql-test/r/func_time.result:
  Task #835: additional changes fot str_to_date
  tests
mysql-test/r/type_time.result:
  Task #835: additional changes fot str_to_date
  tests
mysql-test/t/date_formats.test:
  Task #835: additional changes fot str_to_date
  tests
mysql-test/t/func_sapdb.test:
  Task #835: additional changes fot str_to_date
  tests
mysql-test/t/func_time.test:
  Task #835: additional changes fot str_to_date
  tests
sql/share/czech/errmsg.txt:
  Task #835: additional changes fot str_to_date
  New error message
sql/share/danish/errmsg.txt:
  Task #835: additional changes fot str_to_date
  New error message
sql/share/dutch/errmsg.txt:
  Task #835: additional changes fot str_to_date
  New error message
sql/share/english/errmsg.txt:
  Task #835: additional changes fot str_to_date
  New error message
sql/share/estonian/errmsg.txt:
  Task #835: additional changes fot str_to_date
  New error message
sql/share/french/errmsg.txt:
  Task #835: additional changes fot str_to_date
  New error message
sql/share/german/errmsg.txt:
  Task #835: additional changes fot str_to_date
  New error message
sql/share/greek/errmsg.txt:
  Task #835: additional changes fot str_to_date
  New error message
sql/share/hungarian/errmsg.txt:
  Task #835: additional changes fot str_to_date
  New error message
sql/share/italian/errmsg.txt:
  Task #835: additional changes fot str_to_date
  New error message
sql/share/japanese/errmsg.txt:
  Task #835: additional changes fot str_to_date
  New error message
sql/share/korean/errmsg.txt:
  Task #835: additional changes fot str_to_date
  New error message
sql/share/norwegian-ny/errmsg.txt:
  Task #835: additional changes fot str_to_date
  New error message
sql/share/norwegian/errmsg.txt:
  Task #835: additional changes fot str_to_date
  New error message
sql/share/polish/errmsg.txt:
  Task #835: additional changes fot str_to_date
  New error message
sql/share/portuguese/errmsg.txt:
  Task #835: additional changes fot str_to_date
  New error message
sql/share/romanian/errmsg.txt:
  Task #835: additional changes fot str_to_date
  New error message
sql/share/russian/errmsg.txt:
  Task #835: additional changes fot str_to_date
  New error message
sql/share/serbian/errmsg.txt:
  Task #835: additional changes fot str_to_date
  New error message
sql/share/slovak/errmsg.txt:
  Task #835: additional changes fot str_to_date
  New error message
sql/share/spanish/errmsg.txt:
  Task #835: additional changes fot str_to_date
  New error message
sql/share/swedish/errmsg.txt:
  Task #835: additional changes fot str_to_date
  New error message
sql/share/ukrainian/errmsg.txt:
  Task #835: additional changes fot str_to_date
  New error message

36 files changed, 508 insertions, 143 deletions

diff --git a/include/mysqld_error.h b/include/mysqld_error.h
index 2a7623b6947..77164e637cb 100644
--- a/include/mysqld_error.h
+++ b/include/mysqld_error.h
@@ -308,4 +308,5 @@
 #define ER_FEATURE_DISABLED 1289
 #define ER_OPTION_PREVENTS_STATEMENT 1290
 #define ER_DUPLICATED_VALUE_IN_TYPE 1291
-#define ER_ERROR_MESSAGES 292
+#define ER_TRUNCATED_WRONG_VALUE 1292
+#define ER_ERROR_MESSAGES 293
diff --git a/mysql-test/r/date_formats.result b/mysql-test/r/date_formats.result
index 165a8d7011c..f1582707cf5 100644
--- a/mysql-test/r/date_formats.result
+++ b/mysql-test/r/date_formats.result
@@ -78,11 +78,11 @@ select str_to_date(concat('15-01-2001',' 2:59:58.999'),
 concat('%d-%m-%Y',' ','%H:%i:%s.%f'));
 str_to_date(concat('15-01-2001',' 2:59:58.999'),
 concat('%d-%m-%Y',' ','%H:%i:%s.%f'))
-2001-01-15 02:59:58.000999
+2001-01-15 02:59:58.999000
 create table t1 (date char(30), format char(30) not null);
 insert into t1 values
 ('2003-01-02 10:11:12', '%Y-%m-%d %H:%i:%S'),
-('03-01-02 8:11:2.123456', '%y-%m-%d %H:%i:%S'),
+('03-01-02 8:11:2.123456', '%y-%m-%d %H:%i:%S.%#'),
 ('2003-01-02 10:11:12 PM', '%Y-%m-%d %h:%i:%S %p'),
 ('2003-01-02 01:11:12.12345AM', '%Y-%m-%d %h:%i:%S.%f%p'),
 ('2003-01-02 02:11:12.12345AM', '%Y-%m-%d %h:%i:%S.%f %p'),
@@ -106,16 +106,16 @@ insert into t1 values
 select date,format,str_to_date(date, format) as str_to_date from t1;
 date	format	str_to_date
 2003-01-02 10:11:12	%Y-%m-%d %H:%i:%S	2003-01-02 10:11:12
-03-01-02 8:11:2.123456	%y-%m-%d %H:%i:%S	2003-01-02 08:11:02
+03-01-02 8:11:2.123456	%y-%m-%d %H:%i:%S.%#	2003-01-02 08:11:02
 2003-01-02 10:11:12 PM	%Y-%m-%d %h:%i:%S %p	2003-01-02 22:11:12
-2003-01-02 01:11:12.12345AM	%Y-%m-%d %h:%i:%S.%f%p	2003-01-02 01:11:12.012345
-2003-01-02 02:11:12.12345AM	%Y-%m-%d %h:%i:%S.%f %p	2003-01-02 02:11:12.012345
-2003-01-02 12:11:12.12345 am	%Y-%m-%d %h:%i:%S.%f%p	2003-01-02 00:11:12.012345
+2003-01-02 01:11:12.12345AM	%Y-%m-%d %h:%i:%S.%f%p	2003-01-02 01:11:12.123450
+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
 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:10AM	%h:%i:%s%p	0000-00-00 10:20:10
-10:20:10.44AM	%h:%i:%s.%f%p	0000-00-00 10:20:10.000044
+10:20:10.44AM	%h:%i:%s.%f%p	0000-00-00 10:20:10.440000
 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
@@ -130,16 +130,16 @@ Thursday 53 1998	%W %u %Y	1998-12-31 00:00:00
 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
-03-01-02 8:11:2.123456	%y-%m-%d %H:%i:%S	2003-01-02 08:11:02
+03-01-02 8:11:2.123456	%y-%m-%d %H:%i:%S.%#	2003-01-02 08:11:02
 2003-01-02 10:11:12 PM	%Y-%m-%d %h:%i:%S %p	2003-01-02 22:11:12
-2003-01-02 01:11:12.12345AM	%Y-%m-%d %h:%i:%S.%f%p	2003-01-02 01:11:12.012345
-2003-01-02 02:11:12.12345AM	%Y-%m-%d %h:%i:%S.%f %p	2003-01-02 02:11:12.012345
-2003-01-02 12:11:12.12345 am	%Y-%m-%d %h:%i:%S.%f%p	2003-01-02 00:11:12.012345
+2003-01-02 01:11:12.12345AM	%Y-%m-%d %h:%i:%S.%f%p	2003-01-02 01:11:12.123450
+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
 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:10AM	%h:%i:%s%p	0000-00-00 10:20:10
-10:20:10.44AM	%h:%i:%s.%f%p	0000-00-00 10:20:10.000044
+10:20:10.44AM	%h:%i:%s.%f%p	0000-00-00 10:20:10.440000
 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
@@ -154,16 +154,16 @@ Thursday 53 1998	%W %u %Y	1998-12-31 00:00:00
 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
-03-01-02 8:11:2.123456	%y-%m-%d %H:%i:%S	2003-01-02 08:11:02
+03-01-02 8:11:2.123456	%y-%m-%d %H:%i:%S.%#	2003-01-02 08:11:02
 2003-01-02 10:11:12 PM	%Y-%m-%d %h:%i:%S %p	2003-01-02 22:11:12
-2003-01-02 01:11:12.12345AM	%Y-%m-%d %h:%i:%S.%f%p	2003-01-02 01:11:12.012345
-2003-01-02 02:11:12.12345AM	%Y-%m-%d %h:%i:%S.%f %p	2003-01-02 02:11:12.012345
-2003-01-02 12:11:12.12345 am	%Y-%m-%d %h:%i:%S.%f%p	2003-01-02 00:11:12.012345
+2003-01-02 01:11:12.12345AM	%Y-%m-%d %h:%i:%S.%f%p	2003-01-02 01:11:12.123450
+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
 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:10AM	%h:%i:%s%p	0000-00-00 10:20:10
-10:20:10.44AM	%h:%i:%s.%f%p	0000-00-00 10:20:10.000044
+10:20:10.44AM	%h:%i:%s.%f%p	0000-00-00 10:20:10.440000
 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
@@ -178,7 +178,7 @@ Thursday 53 1998	%W %u %Y	1998-12-31 00:00:00
 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
-03-01-02 8:11:2.123456	%y-%m-%d %H:%i:%S	2003-01-02
+03-01-02 8:11:2.123456	%y-%m-%d %H:%i:%S.%#	2003-01-02
 2003-01-02 10:11:12 PM	%Y-%m-%d %h:%i:%S %p	2003-01-02
 2003-01-02 01:11:12.12345AM	%Y-%m-%d %h:%i:%S.%f%p	2003-01-02
 2003-01-02 02:11:12.12345AM	%Y-%m-%d %h:%i:%S.%f %p	2003-01-02
@@ -202,16 +202,16 @@ Thursday 53 1998	%W %u %Y	1998-12-31
 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
-03-01-02 8:11:2.123456	%y-%m-%d %H:%i:%S	08:11:02
+03-01-02 8:11:2.123456	%y-%m-%d %H:%i:%S.%#	08:11:02
 2003-01-02 10:11:12 PM	%Y-%m-%d %h:%i:%S %p	22:11:12
-2003-01-02 01:11:12.12345AM	%Y-%m-%d %h:%i:%S.%f%p	01:11:12.012345
-2003-01-02 02:11:12.12345AM	%Y-%m-%d %h:%i:%S.%f %p	02:11:12.012345
-2003-01-02 12:11:12.12345 am	%Y-%m-%d %h:%i:%S.%f%p	00:11:12.012345
+2003-01-02 01:11:12.12345AM	%Y-%m-%d %h:%i:%S.%f%p	01:11:12.123450
+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
 10:20:10	%H:%i:%s	10:20:10
 10:20:10	%h:%i:%s.%f	10:20:10
 10:20:10AM	%h:%i:%s%p	10:20:10
-10:20:10.44AM	%h:%i:%s.%f%p	10:20:10.000044
+10:20:10.44AM	%h:%i:%s.%f%p	10:20:10.440000
 15-01-2001 12:59:58	%d-%m-%Y %H:%i:%S	12:59:58
 15 September 2001	%d %M %Y	00:00:00
 15 SEPTEMB 2001	%d %M %Y	00:00:00
@@ -226,16 +226,16 @@ Thursday 53 1998	%W %u %Y	00:00:00
 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
-03-01-02 8:11:2.123456	%y-%m-%d %H:%i:%S	08:11:02
+03-01-02 8:11:2.123456	%y-%m-%d %H:%i:%S.%#	08:11:02
 2003-01-02 10:11:12 PM	%Y-%m-%d %h:%i:%S %p	22:11:12
-2003-01-02 01:11:12.12345AM	%Y-%m-%d %h:%i:%S.%f%p	01:11:12.012345
-2003-01-02 02:11:12.12345AM	%Y-%m-%d %h:%i:%S.%f %p	02:11:12.012345
-2003-01-02 12:11:12.12345 am	%Y-%m-%d %h:%i:%S.%f%p	00:11:12.012345
+2003-01-02 01:11:12.12345AM	%Y-%m-%d %h:%i:%S.%f%p	01:11:12.123450
+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
 10:20:10	%H:%i:%s	10:20:10
 10:20:10	%h:%i:%s.%f	10:20:10
 10:20:10AM	%h:%i:%s%p	10:20:10
-10:20:10.44AM	%h:%i:%s.%f%p	10:20:10.000044
+10:20:10.44AM	%h:%i:%s.%f%p	10:20:10.440000
 15-01-2001 12:59:58	%d-%m-%Y %H:%i:%S	12:59:58
 15 September 2001	%d %M %Y	00:00:00
 15 SEPTEMB 2001	%d %M %Y	00:00:00
@@ -302,11 +302,15 @@ date	format	str_to_date
 10:20:10AM	%h:%i:%s	0000-00-00 10:20:10
 2003-01-02 10:11:12	%Y-%m-%d %h:%i:%S	2003-01-02 10:11:12
 03-01-02 10:11:12 PM	%Y-%m-%d %h:%i:%S %p	0003-01-02 22:11:12
+Warnings:
+Note	1292	Truncated wrong string value: '10:20:10AM'
 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
 2003-01-02 10:11:12	%Y-%m-%d %h:%i:%S	2003-01-02 10:11:12
 03-01-02 10:11:12 PM	%Y-%m-%d %h:%i:%S %p	0003-01-02 22:11:12
+Warnings:
+Note	1292	Truncated wrong string value: '10:20:10AM'
 drop table t1;
 select get_format(DATE, 'USA') as a;
 a
@@ -335,3 +339,56 @@ date_format(d,"%d")
 14
 14
 drop table t1;
+select str_to_date("2003-....01ABCD-02 10:11:12.0012", "%Y-%.%m%@-%d %H:%i:%S.%f") as a;
+a
+2003-01-02 10:11:12.001200
+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;
+describe t1;
+Field	Type	Null	Key	Default	Extra
+f1	datetime	YES		NULL	
+f2	time	YES		NULL	
+f3	date	YES		NULL	
+f4	date	YES		NULL	
+f5	time	YES		NULL	
+select * from t1;
+f1	f2	f3	f4	f5
+2003-01-02 10:11:12	10:11:12	2003-01-02	0000-00-02	58:00:00
+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
+create table t2 select str_to_date(a,b) from t1;
+describe t2;
+Field	Type	Null	Key	Default	Extra
+str_to_date(a,b)	char(29)	YES		NULL	
+select str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S.%f") as f1,
+str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S") as f2,
+str_to_date("2003-01-02", "%Y-%m-%d") as f3,
+str_to_date("02 10:11:12", "%d %H:%i:%S.%f") as f4,
+str_to_date("02 10:11:12", "%d %H:%i:%S") as f5,
+str_to_date("02 10", "%d %f") as f6;
+f1	f2	f3	f4	f5	f6
+2003-01-02 10:11:12.001200	2003-01-02 10:11:12	2003-01-02	58:11:12	58:11:12	48:00:00.100000
+Warnings:
+Note	1292	Truncated wrong datetime value: '2003-01-02 10:11:12.0012'
+drop table t1, t2;
+select str_to_date("2003-01-02 10:11:12.0012ABCD", "%Y-%m-%d %H:%i:%S.%f") as f1,
+addtime("-01:01:01.01 GGG", "-23:59:59.1") as f2,
+microsecond("1997-12-31 23:59:59.01XXXX") as f3;
+f1	f2	f3
+2003-01-02 10:11:12.001200	-25:01:00.110000	10000
+Warnings:
+Note	1292	Truncated wrong datetime value: '2003-01-02 10:11:12.0012ABCD'
+Note	1292	Truncated wrong time value: '-01:01:01.01 GG'
+Note	1292	Truncated wrong datetime value: '1997-12-31 23:59:59.01XXXX'
+select str_to_date("2003-04-05  g", "%Y-%m-%d") as f1,
+str_to_date("2003-04-05 10:11:12.101010234567", "%Y-%m-%d %H:%i:%S.%f") as f2;
+f1	f2
+2003-04-05	2003-04-05 10:11:12.101010
+Warnings:
+Note	1292	Truncated wrong date value: '2003-04-05  g'
+Note	1292	Truncated wrong datetime value: '2003-04-05 10:11:12.101010234567'
diff --git a/mysql-test/r/func_sapdb.result b/mysql-test/r/func_sapdb.result
index 38bd73bca0f..31868261157 100644
--- a/mysql-test/r/func_sapdb.result
+++ b/mysql-test/r/func_sapdb.result
@@ -198,3 +198,18 @@ NULL	NULL
 NULL	NULL
 00:00:00	-24:00:00
 drop table t1, test;
+select addtime("-01:01:01.01", "-23:59:59.1") as a;
+a
+-25:01:00.110000
+select microsecond("1997-12-31 23:59:59.01") as a;
+a
+10000
+select microsecond(19971231235959.01) as a;
+a
+10000
+select date_add("1997-12-31",INTERVAL "10.09" SECOND_MICROSECOND) as a;
+a
+1997-12-31 00:00:10.090000
+select str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S.%f");
+str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S.%f")
+2003-01-02 10:11:12.001200
diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result
index 0998f7b8bcf..060a5d0f1bd 100644
--- a/mysql-test/r/func_time.result
+++ b/mysql-test/r/func_time.result
@@ -506,17 +506,32 @@ last_day('2001-01-01 01:01:01') as f5, last_day(NULL),
 last_day('2001-02-12');
 f1	f2	f3	f4	f5	last_day(NULL)	last_day('2001-02-12')
 2000-02-29	2002-12-31	NULL	2003-04-30	2001-01-31	NULL	2001-02-28
-create table t1 select last_day('2000-02-05') as a;
+create table t1 select last_day('2000-02-05') as a,
+from_days(to_days("960101")) as b;
 describe t1;
 Field	Type	Null	Key	Default	Extra
 a	date			0000-00-00	
+b	date	YES		NULL	
 select * from t1;
-a
-2000-02-29
+a	b
+2000-02-29	1996-01-01
 drop table t1;
-select last_day('2000-02-05');
-last_day('2000-02-05')
-2000-02-29
+select last_day('2000-02-05') as a,
+from_days(to_days("960101")) as b;
+a	b
+2000-02-29	1996-01-01
+select date_add(last_day("1997-12-1"), INTERVAL 1 DAY);
+date_add(last_day("1997-12-1"), INTERVAL 1 DAY)
+1998-01-01
+select length(last_day("1997-12-1"));
+length(last_day("1997-12-1"))
+10
+select last_day("1997-12-1")+0;
+last_day("1997-12-1")+0
+19971231
+select last_day("1997-12-1")+0.0;
+last_day("1997-12-1")+0.0
+19971231.0
 select strcmp(date_sub(localtimestamp(), interval 3 hour), utc_timestamp())=0;
 strcmp(date_sub(localtimestamp(), interval 3 hour), utc_timestamp())=0
 1
diff --git a/mysql-test/r/type_time.result b/mysql-test/r/type_time.result
index 0830179902d..68b56802120 100644
--- a/mysql-test/r/type_time.result
+++ b/mysql-test/r/type_time.result
@@ -25,9 +25,11 @@ t
 36:30:31
 insert into t1 values("10.22.22"),(1234567),(123456789),(123456789.10),("10 22:22"),("12.45a");
 Warnings:
+Note	1292	Truncated wrong time value: '10.22.22'
 Warning	1264	Data truncated, out of range for column 't' at row 2
 Warning	1264	Data truncated, out of range for column 't' at row 3
 Warning	1264	Data truncated, out of range for column 't' at row 4
+Note	1292	Truncated wrong time value: '12.45a'
 select * from t1;
 t
 10:22:33
diff --git a/mysql-test/t/date_formats.test b/mysql-test/t/date_formats.test
index 18af3dfb3db..1fc04cb907b 100644
--- a/mysql-test/t/date_formats.test
+++ b/mysql-test/t/date_formats.test
@@ -124,7 +124,7 @@ select str_to_date(concat('15-01-2001',' 2:59:58.999'),
 create table t1 (date char(30), format char(30) not null);
 insert into t1 values
 ('2003-01-02 10:11:12', '%Y-%m-%d %H:%i:%S'),
-('03-01-02 8:11:2.123456', '%y-%m-%d %H:%i:%S'),
+('03-01-02 8:11:2.123456', '%y-%m-%d %H:%i:%S.%#'),
 ('2003-01-02 10:11:12 PM', '%Y-%m-%d %h:%i:%S %p'),
 ('2003-01-02 01:11:12.12345AM', '%Y-%m-%d %h:%i:%S.%f%p'),
 ('2003-01-02 02:11:12.12345AM', '%Y-%m-%d %h:%i:%S.%f %p'),
@@ -209,3 +209,32 @@ create table t1 (d date);
 insert into t1 values ('2004-07-14'),('2005-07-14');
 select date_format(d,"%d") from t1 order by 1;
 drop table t1;
+
+select str_to_date("2003-....01ABCD-02 10:11:12.0012", "%Y-%.%m%@-%d %H:%i:%S.%f") as a;
+
+
+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;
+describe t1;
+select * from t1;
+drop table t1;
+
+create table t1 select "02 10" as a, "%d %H" as b;
+select str_to_date(a,b) from t1;
+create table t2 select str_to_date(a,b) from t1;
+describe t2;
+select str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S.%f") as f1,
+       str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S") as f2,
+       str_to_date("2003-01-02", "%Y-%m-%d") as f3,
+       str_to_date("02 10:11:12", "%d %H:%i:%S.%f") as f4,
+       str_to_date("02 10:11:12", "%d %H:%i:%S") as f5,
+       str_to_date("02 10", "%d %f") as f6;
+drop table t1, t2;
+select str_to_date("2003-01-02 10:11:12.0012ABCD", "%Y-%m-%d %H:%i:%S.%f") as f1,
+       addtime("-01:01:01.01 GGG", "-23:59:59.1") as f2,
+       microsecond("1997-12-31 23:59:59.01XXXX") as f3;
+
+select str_to_date("2003-04-05  g", "%Y-%m-%d") as f1,
+       str_to_date("2003-04-05 10:11:12.101010234567", "%Y-%m-%d %H:%i:%S.%f") as f2;
diff --git a/mysql-test/t/func_sapdb.test b/mysql-test/t/func_sapdb.test
index afd84fe9630..24028437fde 100644
--- a/mysql-test/t/func_sapdb.test
+++ b/mysql-test/t/func_sapdb.test
@@ -97,3 +97,9 @@ SELECT ADDTIME(t1,t2) As ttt, ADDTIME(t2, t3) As qqq from test;
 SELECT TIMEDIFF(t1,t4) As ttt, TIMEDIFF(t2, t3) As qqq from test;
 
 drop table t1, test;
+
+select addtime("-01:01:01.01", "-23:59:59.1") as a;
+select microsecond("1997-12-31 23:59:59.01") as a;
+select microsecond(19971231235959.01) as a;
+select date_add("1997-12-31",INTERVAL "10.09" SECOND_MICROSECOND) as a;
+select str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S.%f");
diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test
index 06d0ff3fa1d..6417d5448e5 100644
--- a/mysql-test/t/func_time.test
+++ b/mysql-test/t/func_time.test
@@ -249,12 +249,18 @@ select last_day('2000-02-05') as f1, last_day('2002-12-31') as f2,
        last_day('2001-01-01 01:01:01') as f5, last_day(NULL),
        last_day('2001-02-12');
 
-create table t1 select last_day('2000-02-05') as a;
+create table t1 select last_day('2000-02-05') as a,
+                from_days(to_days("960101")) as b;
 describe t1;
 select * from t1;
 drop table t1;
-select last_day('2000-02-05');
+select last_day('2000-02-05') as a,
+       from_days(to_days("960101")) as b;
 
+select date_add(last_day("1997-12-1"), INTERVAL 1 DAY);
+select length(last_day("1997-12-1"));
+select last_day("1997-12-1")+0;
+select last_day("1997-12-1")+0.0;
 
 # Test SAPDB UTC_% functions. This part is TZ dependant (It is supposed that
 # TZ variable set to GMT-3
diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc
index 32fbe192d8f..d0674411fcf 100644
--- a/sql/item_timefunc.cc
+++ b/sql/item_timefunc.cc
@@ -48,11 +48,6 @@ TYPELIB day_names_typelib=
 { array_elements(day_names)-1,"", day_names};
 
 
-enum date_time_format_types 
-{ 
-  TIME_ONLY= 0, TIME_MICROSECOND, DATE_ONLY, DATE_TIME, DATE_TIME_MICROSECOND
-};
-
 /*
   OPTIMIZATION TODO:
    - Replace the switch with a function that should be called for each
@@ -128,6 +123,9 @@ static bool make_datetime(date_time_format_types format, TIME *ltime,
     val			String to decode
     length		Length of string
     l_time		Store result here
+    cached_timestamp_type 
+                       It uses to get an appropriate warning
+                       in the case when the value is truncated.
 
     RETURN
       0	ok
@@ -135,7 +133,8 @@ static bool make_datetime(date_time_format_types format, TIME *ltime,
 */
 
 static bool extract_date_time(DATE_TIME_FORMAT *format,
-			      const char *val, uint length, TIME *l_time)
+			      const char *val, uint length, TIME *l_time,
+			      timestamp_type cached_timestamp_type)
 {
   int weekday= 0, yearday= 0, daypart= 0;
   int week_number= -1;
@@ -143,9 +142,11 @@ static bool extract_date_time(DATE_TIME_FORMAT *format,
   int error= 0;
   bool usa_time= 0;
   bool sunday_first= 0;
+  int frac_part;
+  const char *val_begin= val;
   const char *val_end= val + length;
   const char *ptr= format->format.str;
-  const char *end= ptr+ format->format.length;
+  const char *end= ptr + format->format.length;
   DBUG_ENTER("extract_date_time");
 
   bzero((char*) l_time, sizeof(*l_time));
@@ -235,7 +236,12 @@ static bool extract_date_time(DATE_TIME_FORMAT *format,
 	/* Second part */
       case 'f':
 	tmp= (char*) val_end;
+	if (tmp - val > 6)
+	  tmp= (char*) val + 6;
 	l_time->second_part= (int) my_strtoll10(val, &tmp, &error);
+	frac_part= 6 - (tmp - val);
+	if (frac_part > 0)
+	  l_time->second_part*= (ulong) log_10_int[frac_part];
 	val= tmp;
 	break;
 
@@ -251,6 +257,7 @@ static bool extract_date_time(DATE_TIME_FORMAT *format,
 			      (const uchar *) val, 2, 
 			      (const uchar *) "AM", 2))
 	  goto err;
+	val+= 2;
 	break;
 
 	/* Exotic things */
@@ -281,6 +288,18 @@ static bool extract_date_time(DATE_TIME_FORMAT *format,
 	val= tmp;
 	break;
 
+      case '.':
+	while (my_ispunct(cs, *val) && val != val_end)
+	  val++;
+	break;
+      case '@':
+	while (my_isalpha(cs, *val) && val != val_end)
+	  val++;
+	break;
+      case '#':
+	while (my_isdigit(cs, *val) && val != val_end)
+	  val++;
+	break;
       default:
 	goto err;
       }
@@ -348,6 +367,18 @@ static bool extract_date_time(DATE_TIME_FORMAT *format,
       l_time->minute > 59 || l_time->second > 59)
     goto err;
 
+  if (val != val_end)
+  {
+    do
+    {
+      if (!my_isspace(&my_charset_latin1,*val))
+      {
+	make_truncated_value_warning(current_thd, val_begin, length,
+				     cached_timestamp_type);
+	break;
+      }
+    } while (++val != val_end);
+  }
   DBUG_RETURN(0);
 
 err:
@@ -584,16 +615,27 @@ bool make_date_time(DATE_TIME_FORMAT *format, TIME *l_time,
 
 
 /*
-** Get a array of positive numbers from a string object.
-** Each number is separated by 1 non digit character
-** Return error if there is too many numbers.
-** If there is too few numbers, assume that the numbers are left out
-** from the high end. This allows one to give:
-** DAY_TO_SECOND as "D MM:HH:SS", "MM:HH:SS" "HH:SS" or as seconds.
+  Get a array of positive numbers from a string object.
+  Each number is separated by 1 non digit character
+  Return error if there is too many numbers.
+  If there is too few numbers, assume that the numbers are left out
+  from the high end. This allows one to give:
+  DAY_TO_SECOND as "D MM:HH:SS", "MM:HH:SS" "HH:SS" or as seconds.
+
+  SYNOPSIS
+    str:            string value
+    length:         length of str
+    cs:             charset of str
+    values:         array of results
+    count:          count of elements in result array
+    transform_msec: if value is true we suppose
+                    that the last part of string value is microseconds
+                    and we should transform value to six digit value.
+                    For example, '1.1' -> '1.100000'
 */
 
 bool get_interval_info(const char *str,uint length,CHARSET_INFO *cs,
-			uint count, long *values)
+                       uint count, long *values, bool transform_msec)
 {
   const char *end=str+length;
   uint i;
@@ -603,8 +645,15 @@ bool get_interval_info(const char *str,uint length,CHARSET_INFO *cs,
   for (i=0 ; i < count ; i++)
   {
     long value;
+    const char *start= str;
     for (value=0; str != end && my_isdigit(cs,*str) ; str++)
       value=value*10L + (long) (*str - '0');
+    if (transform_msec && i == count - 1) // microseconds always last
+    {
+      long msec_length= 6 - (str - start);
+      if (msec_length > 0)
+	value*= (long) log_10_int[msec_length];
+    }
     values[i]= value;
     while (str != end && !my_isdigit(cs,*str))
       str++;
@@ -925,19 +974,19 @@ static bool get_interval_value(Item *args,interval_type int_type,
     interval->second=value;
     break;
   case INTERVAL_YEAR_MONTH:			// Allow YEAR-MONTH YYYYYMM
-    if (get_interval_info(str,length,cs,2,array))
+    if (get_interval_info(str,length,cs,2,array,0))
       return (1);
     interval->year=array[0];
     interval->month=array[1];
     break;
   case INTERVAL_DAY_HOUR:
-    if (get_interval_info(str,length,cs,2,array))
+    if (get_interval_info(str,length,cs,2,array,0))
       return (1);
     interval->day=array[0];
     interval->hour=array[1];
     break;
   case INTERVAL_DAY_MICROSECOND:
-    if (get_interval_info(str,length,cs,5,array))
+    if (get_interval_info(str,length,cs,5,array,1))
       return (1);
     interval->day=array[0];
     interval->hour=array[1];
@@ -946,14 +995,14 @@ static bool get_interval_value(Item *args,interval_type int_type,
     interval->second_part=array[4];
     break;
   case INTERVAL_DAY_MINUTE:
-    if (get_interval_info(str,length,cs,3,array))
+    if (get_interval_info(str,length,cs,3,array,0))
       return (1);
     interval->day=array[0];
     interval->hour=array[1];
     interval->minute=array[2];
     break;
   case INTERVAL_DAY_SECOND:
-    if (get_interval_info(str,length,cs,4,array))
+    if (get_interval_info(str,length,cs,4,array,0))
       return (1);
     interval->day=array[0];
     interval->hour=array[1];
@@ -961,7 +1010,7 @@ static bool get_interval_value(Item *args,interval_type int_type,
     interval->second=array[3];
     break;
   case INTERVAL_HOUR_MICROSECOND:
-    if (get_interval_info(str,length,cs,4,array))
+    if (get_interval_info(str,length,cs,4,array,1))
       return (1);
     interval->hour=array[0];
     interval->minute=array[1];
@@ -969,33 +1018,33 @@ static bool get_interval_value(Item *args,interval_type int_type,
     interval->second_part=array[3];
     break;
   case INTERVAL_HOUR_MINUTE:
-    if (get_interval_info(str,length,cs,2,array))
+    if (get_interval_info(str,length,cs,2,array,0))
       return (1);
     interval->hour=array[0];
     interval->minute=array[1];
     break;
   case INTERVAL_HOUR_SECOND:
-    if (get_interval_info(str,length,cs,3,array))
+    if (get_interval_info(str,length,cs,3,array,0))
       return (1);
     interval->hour=array[0];
     interval->minute=array[1];
     interval->second=array[2];
     break;
   case INTERVAL_MINUTE_MICROSECOND:
-    if (get_interval_info(str,length,cs,3,array))
+    if (get_interval_info(str,length,cs,3,array,1))
       return (1);
     interval->minute=array[0];
     interval->second=array[1];
     interval->second_part=array[2];
     break;
   case INTERVAL_MINUTE_SECOND:
-    if (get_interval_info(str,length,cs,2,array))
+    if (get_interval_info(str,length,cs,2,array,0))
       return (1);
     interval->minute=array[0];
     interval->second=array[1];
     break;
   case INTERVAL_SECOND_MICROSECOND:
-    if (get_interval_info(str,length,cs,2,array))
+    if (get_interval_info(str,length,cs,2,array,1))
       return (1);
     interval->second=array[0];
     interval->second_part=array[1];
@@ -1008,22 +1057,13 @@ static bool get_interval_value(Item *args,interval_type int_type,
 String *Item_date::val_str(String *str)
 {
   TIME ltime;
-  ulong value=(ulong) val_int();
-  if (null_value)
-    return (String*) 0;
-
+  if (get_date(&ltime, TIME_FUZZY_DATE))
+    return (String *) 0;
   if (str->alloc(11))
   {
     null_value= 1;
     return (String *) 0;
   }
-
-  ltime.year=	(value/10000L) % 10000;
-  ltime.month=	(value/100)%100;
-  ltime.day=	(value%100);
-  ltime.neg= 0;
-  ltime.time_type=TIMESTAMP_DATE;
-
   make_date((DATE_TIME_FORMAT *) 0, &ltime, str);
   return str;
 }
@@ -1032,28 +1072,31 @@ String *Item_date::val_str(String *str)
 int Item_date::save_in_field(Field *field, bool no_conversions)
 {
   TIME ltime;
-  timestamp_type t_type=TIMESTAMP_DATETIME;
   if (get_date(&ltime, TIME_FUZZY_DATE))
-  {
-    if (null_value)
-      return set_field_to_null(field);
-    t_type=TIMESTAMP_NONE;			// Error
-  }
+    return set_field_to_null(field);
   field->set_notnull();
-  field->store_time(&ltime,t_type);
+  field->store_time(&ltime, TIMESTAMP_DATE);
   return 0;
 }
 
 
-longlong Item_func_from_days::val_int()
+longlong Item_date::val_int()
+{
+  TIME ltime;
+  if (get_date(&ltime, TIME_FUZZY_DATE))
+    return 0;
+  return (longlong) (ltime.year*10000L+ltime.month*100+ltime.day);
+}
+
+
+bool Item_func_from_days::get_date(TIME *ltime, uint fuzzy_date)
 {
   longlong value=args[0]->val_int();
   if ((null_value=args[0]->null_value))
-    return 0; /* purecov: inspected */
-
-  uint year,month,day;
-  get_date_from_daynr((long) value,&year,&month,&day);
-  return (longlong) (year*10000L+month*100+day);
+    return 1;
+  get_date_from_daynr((long) value, &ltime->year, &ltime->month, &ltime->day);
+  ltime->time_type= TIMESTAMP_DATE;
+  return 0;
 }
 
 
@@ -1082,6 +1125,16 @@ void Item_func_curdate::fix_length_and_dec()
   ltime.time_type=TIMESTAMP_DATE;
 }
 
+String *Item_func_curdate::val_str(String *str)
+{
+  if (str->alloc(11))
+  {
+    null_value= 1;
+    return (String *) 0;
+  }
+  make_date((DATE_TIME_FORMAT *) 0, &ltime, str);
+  return str;
+}
 
 bool Item_func_curdate::get_date(TIME *res,
 				 uint fuzzy_date __attribute__((unused)))
@@ -2311,6 +2364,103 @@ void Item_func_get_format::print(String *str)
 }
 
 
+/*
+  check_result_type(s, l) returns DATE/TIME type
+  according to format string
+
+  s: DATE/TIME format string
+  l: length of s
+  Result: date_time_format_types value:
+          DATE_TIME_MICROSECOND, DATE_TIME,
+          TIME_MICROSECOND, TIME_ONLY
+
+  We don't process day format's characters('D', 'd', 'e')
+  because day may be a member of all date/time types.
+  If only day format's character and no time part present
+  the result type is MYSQL_TYPE_DATE
+*/
+
+date_time_format_types  check_result_type(const char *format, uint length)
+{
+  const char *time_part_frms= "HISThiklrs";
+  const char *date_part_frms= "MUYWabcjmuyw";
+  bool date_part_used= 0, time_part_used= 0, frac_second_used= 0;
+  
+  const char *val= format;
+  const char *end= format + length;
+
+  for (; val != end && val != end; val++)
+  {
+    if (*val == '%' && val+1 != end)
+    {
+      val++;
+      if ((frac_second_used= (*val == 'f')) ||
+	  (!time_part_used && strchr(time_part_frms, *val)))
+	time_part_used= 1;
+      else if (!date_part_used && strchr(date_part_frms, *val))
+	date_part_used= 1;
+      if (time_part_used && date_part_used && frac_second_used)
+	return DATE_TIME_MICROSECOND;
+    }
+  }
+
+  if (time_part_used)
+  {
+    if (date_part_used)
+      return DATE_TIME;
+    if (frac_second_used)
+      return TIME_MICROSECOND;
+    return TIME_ONLY;
+  }
+  return DATE_ONLY;
+}
+
+
+Field *Item_func_str_to_date::tmp_table_field(TABLE *t_arg)
+{
+  if (cached_field_type == MYSQL_TYPE_TIME)
+    return (new Field_time(maybe_null, name, t_arg, &my_charset_bin));
+  if (cached_field_type == MYSQL_TYPE_DATE)
+    return (new Field_date(maybe_null, name, t_arg, &my_charset_bin));
+  if (cached_field_type == MYSQL_TYPE_DATETIME)
+    return (new Field_datetime(maybe_null, name, t_arg, &my_charset_bin));
+  return (new Field_string(max_length, maybe_null, name, t_arg, &my_charset_bin));
+}
+
+
+void Item_func_str_to_date::fix_length_and_dec()
+{
+  char format_buff[64];
+  String format_str(format_buff, sizeof(format_buff), &my_charset_bin), *format;
+  maybe_null= 1;
+  decimals=0;
+  cached_field_type= MYSQL_TYPE_STRING;
+  max_length= MAX_DATETIME_FULL_WIDTH*MY_CHARSET_BIN_MB_MAXLEN;
+  cached_timestamp_type= TIMESTAMP_NONE;
+  if ((const_item= args[1]->const_item()))
+  {
+    format= args[1]->val_str(&format_str);
+    cached_format_type= check_result_type(format->ptr(), format->length());
+    switch (cached_format_type) {
+    case DATE_ONLY:
+      cached_timestamp_type= TIMESTAMP_DATE;
+      cached_field_type= MYSQL_TYPE_DATE; 
+      max_length= MAX_DATE_WIDTH*MY_CHARSET_BIN_MB_MAXLEN;
+      break;
+    case TIME_ONLY:
+    case TIME_MICROSECOND:
+      cached_timestamp_type= TIMESTAMP_TIME;
+      cached_field_type= MYSQL_TYPE_TIME; 
+      max_length= MAX_TIME_WIDTH*MY_CHARSET_BIN_MB_MAXLEN;
+      break;
+    default:
+      cached_timestamp_type= TIMESTAMP_DATETIME;
+      cached_field_type= MYSQL_TYPE_DATETIME; 
+      break;
+    }
+  }
+}
+
 bool Item_func_str_to_date::get_date(TIME *ltime, uint fuzzy_date)
 {
   DATE_TIME_FORMAT date_time_format;
@@ -2328,8 +2478,18 @@ bool Item_func_str_to_date::get_date(TIME *ltime, uint fuzzy_date)
   date_time_format.format.str=    (char*) format->ptr();
   date_time_format.format.length= format->length();
   if (extract_date_time(&date_time_format, val->ptr(), val->length(),
-			ltime))
+			ltime, cached_timestamp_type))
     goto null_date;
+  if (cached_timestamp_type == TIMESTAMP_TIME && ltime->day)
+  {
+    /*
+      Day part for time type can be nonzero value and so 
+      we should add hours from day part to hour part to
+      keep valid time value.
+    */
+    ltime->hour+= ltime->day*24;
+    ltime->day= 0;
+  }
   return 0;
 
 null_date:
@@ -2344,29 +2504,22 @@ String *Item_func_str_to_date::val_str(String *str)
   if (Item_func_str_to_date::get_date(&ltime, TIME_FUZZY_DATE))
     return 0;
 
-  /*
-    The following DATE_TIME should be done dynamicly based on the
-    format string (wen it's a constant). For example, we should only return
-    microseconds if there was an %f in the format
-  */
-  if (!make_datetime(ltime.second_part ? DATE_TIME_MICROSECOND : DATE_TIME,
+  if (!make_datetime((const_item ? cached_format_type :
+		     (ltime.second_part ? DATE_TIME_MICROSECOND : DATE_TIME)),
 		     &ltime, str))
     return str;
   return 0;
 }
 
 
-String *Item_func_last_day::val_str(String *str)
+bool Item_func_last_day::get_date(TIME *ltime, uint fuzzy_date)
 {
-  TIME ltime;
-  if (!get_arg0_date(&ltime,0))
-  {
-    uint month_idx= ltime.month-1;
-    ltime.day= days_in_month[month_idx];
-    if ( month_idx == 1 && calc_days_in_year(ltime.year) == 366)
-      ltime.day+= 1;
-    if (!make_datetime(DATE_ONLY, &ltime, str))
-      return str;      
-  }
+  if (get_arg0_date(ltime,fuzzy_date))
+    return 1;
+  uint month_idx= ltime->month-1;
+  ltime->day= days_in_month[month_idx];
+  if ( month_idx == 1 && calc_days_in_year(ltime->year) == 366)
+    ltime->day= 29;
+  ltime->time_type= TIMESTAMP_DATE;
   return 0;
 }
diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h
index 854a54bbe80..1b044b49fb1 100644
--- a/sql/item_timefunc.h
+++ b/sql/item_timefunc.h
@@ -21,6 +21,11 @@
 #pragma interface			/* gcc class implementation */
 #endif
 
+enum date_time_format_types 
+{ 
+  TIME_ONLY= 0, TIME_MICROSECOND, DATE_ONLY, DATE_TIME, DATE_TIME_MICROSECOND
+};
+
 class Item_func_period_add :public Item_int_func
 {
 public:
@@ -318,6 +323,7 @@ public:
   enum Item_result result_type () const { return STRING_RESULT; }
   enum_field_types field_type() const { return MYSQL_TYPE_DATE; }
   String *val_str(String *str);
+  longlong val_int();
   double val() { return (double) val_int(); }
   const char *func_name() const { return "date"; }
   void fix_length_and_dec()
@@ -407,6 +413,7 @@ public:
   Item_func_curdate() :Item_date() {}
   void set_result_from_tm(struct tm *now);
   longlong val_int() { return (value) ; }
+  String *val_str(String *str);
   void fix_length_and_dec();
   bool get_date(TIME *res, uint fuzzy_date);
   virtual void store_now_in_tm(time_t now, struct tm *now_tm)=0;
@@ -477,8 +484,8 @@ class Item_func_from_days :public Item_date
 {
 public:
   Item_func_from_days(Item *a) :Item_date(a) {}
-  longlong val_int();
   const char *func_name() const { return "from_days"; }
+  bool get_date(TIME *res, uint fuzzy_date);
 };
 
 
@@ -806,37 +813,29 @@ public:
 };
 
 
-class Item_func_str_to_date :public Item_date_func
+class Item_func_str_to_date :public Item_str_func
 {
+  enum_field_types cached_field_type;
+  date_time_format_types cached_format_type;
+  timestamp_type cached_timestamp_type;
+  bool const_item;
 public:
   Item_func_str_to_date(Item *a, Item *b)
-    :Item_date_func(a, b)
+    :Item_str_func(a, b)
   {}
   String *val_str(String *str);
   bool get_date(TIME *ltime, uint fuzzy_date);
   const char *func_name() const { return "str_to_date"; }
-  void fix_length_and_dec()
-  {
-    maybe_null= 1;
-    decimals=0;
-    max_length=MAX_DATETIME_FULL_WIDTH*MY_CHARSET_BIN_MB_MAXLEN;
-  }
+  enum_field_types field_type() const { return cached_field_type; }
+  void fix_length_and_dec();
+  Field *tmp_table_field(TABLE *t_arg);
 };
 
-class Item_func_last_day :public Item_str_func
+
+class Item_func_last_day :public Item_date
 {
 public:
-  Item_func_last_day(Item *a) :Item_str_func(a) {}
-  String *val_str(String *str);
+  Item_func_last_day(Item *a) :Item_date(a) {}
   const char *func_name() const { return "last_day"; }
-  enum_field_types field_type() const { return MYSQL_TYPE_DATE; }
-  void fix_length_and_dec()
-  { 
-    decimals=0;
-    max_length=MAX_DATE_WIDTH*MY_CHARSET_BIN_MB_MAXLEN;
-  }
-  Field *tmp_table_field(TABLE *t_arg)
-  {
-    return (new Field_date(maybe_null, name, t_arg, &my_charset_bin));
-  }
+  bool get_date(TIME *res, uint fuzzy_date);
 };
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index eb879a1fd59..9fd7e6f5b93 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -799,6 +799,7 @@ extern char glob_hostname[FN_REFLEN], mysql_home[FN_REFLEN];
 extern char pidfile_name[FN_REFLEN], time_zone[30], *opt_init_file;
 extern char log_error_file[FN_REFLEN];
 extern double log_10[32];
+extern ulonglong log_10_int[20];
 extern ulonglong keybuff_size;
 extern ulong refresh_version,flush_version, thread_id,query_id,opened_tables;
 extern ulong created_tmp_tables, created_tmp_disk_tables, bytes_sent;
@@ -958,6 +959,8 @@ timestamp_type str_to_TIME(const char *str, uint length, TIME *l_time,
 void localtime_to_TIME(TIME *to, struct tm *from);
 void calc_time_from_sec(TIME *to, long seconds, long microseconds);
 
+void make_truncated_value_warning(THD *thd, const char *str_val,
+				  uint str_length, timestamp_type time_type);
 extern DATE_TIME_FORMAT *date_time_format_make(timestamp_type format_type,
 					       const char *format_str,
 					       uint format_length);
diff --git a/sql/mysqld.cc b/sql/mysqld.cc
index 5b6c592cd51..9a469e529cf 100644
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@ -305,6 +305,14 @@ ulong my_bind_addr;			/* the address we bind to */
 volatile ulong cached_thread_count= 0;
 
 double log_10[32];			/* 10 potences */
+ulonglong log_10_int[20]=
+{
+  1, 10, 100, 1000, 10000UL, 100000UL, 1000000UL, 10000000UL,
+  100000000UL, 1000000000UL, 10000000000UL, 100000000000UL,
+  1000000000000UL, 10000000000000UL, 100000000000000UL, 
+  1000000000000000UL, 10000000000000000UL, 100000000000000000UL,
+  1000000000000000000UL, 10000000000000000000UL
+};
 
 time_t start_time;
 
diff --git a/sql/share/czech/errmsg.txt b/sql/share/czech/errmsg.txt
index 3a2d7a44c44..2d2a4472d9d 100644
--- a/sql/share/czech/errmsg.txt
+++ b/sql/share/czech/errmsg.txt
@@ -304,3 +304,4 @@ character-set=latin2
 "The '%s' feature was disabled; you need MySQL built with '%s' to have it working",
 "The MySQL server is running with the %s option so it cannot execute this statement",
 "Column '%-.100s' has duplicated value '%-.64s' in %s"
+"Truncated wrong %-.32s value: '%-.128s'"
diff --git a/sql/share/danish/errmsg.txt b/sql/share/danish/errmsg.txt
index 917ea3bf407..ca7c67552c3 100644
--- a/sql/share/danish/errmsg.txt
+++ b/sql/share/danish/errmsg.txt
@@ -298,3 +298,4 @@ character-set=latin1
 "The '%s' feature was disabled; you need MySQL built with '%s' to have it working",
 "The MySQL server is running with the %s option so it cannot execute this statement",
 "Column '%-.100s' has duplicated value '%-.64s' in %s"
+"Truncated wrong %-.32s value: '%-.128s'"
diff --git a/sql/share/dutch/errmsg.txt b/sql/share/dutch/errmsg.txt
index 16cf2fd15de..2cb9e7e511b 100644
--- a/sql/share/dutch/errmsg.txt
+++ b/sql/share/dutch/errmsg.txt
@@ -306,3 +306,4 @@ character-set=latin1
 "The '%s' feature was disabled; you need MySQL built with '%s' to have it working",
 "The MySQL server is running with the %s option so it cannot execute this statement",
 "Column '%-.100s' has duplicated value '%-.64s' in %s"
+"Truncated wrong %-.32s value: '%-.128s'"
diff --git a/sql/share/english/errmsg.txt b/sql/share/english/errmsg.txt
index 8c4e0530ed4..4e0da64408e 100644
--- a/sql/share/english/errmsg.txt
+++ b/sql/share/english/errmsg.txt
@@ -295,3 +295,4 @@ character-set=latin1
 "The '%s' feature was disabled; you need MySQL built with '%s' to have it working",
 "The MySQL server is running with the %s option so it cannot execute this statement",
 "Column '%-.100s' has duplicated value '%-.64s' in %s"
+"Truncated wrong %-.32s value: '%-.128s'"
diff --git a/sql/share/estonian/errmsg.txt b/sql/share/estonian/errmsg.txt
index 8c7cc53ef06..8b912d10e97 100644
--- a/sql/share/estonian/errmsg.txt
+++ b/sql/share/estonian/errmsg.txt
@@ -300,3 +300,4 @@ character-set=latin7
 "The '%s' feature was disabled; you need MySQL built with '%s' to have it working",
 "The MySQL server is running with the %s option so it cannot execute this statement",
 "Column '%-.100s' has duplicated value '%-.64s' in %s"
+"Truncated wrong %-.32s value: '%-.128s'"
diff --git a/sql/share/french/errmsg.txt b/sql/share/french/errmsg.txt
index 78d729fc9f8..ef7385422fb 100644
--- a/sql/share/french/errmsg.txt
+++ b/sql/share/french/errmsg.txt
@@ -295,3 +295,4 @@ character-set=latin1
 "The '%s' feature was disabled; you need MySQL built with '%s' to have it working",
 "The MySQL server is running with the %s option so it cannot execute this statement",
 "Column '%-.100s' has duplicated value '%-.64s' in %s"
+"Truncated wrong %-.32s value: '%-.128s'"
diff --git a/sql/share/german/errmsg.txt b/sql/share/german/errmsg.txt
index 142076a1f14..60454d0d011 100644
--- a/sql/share/german/errmsg.txt
+++ b/sql/share/german/errmsg.txt
@@ -307,3 +307,4 @@ character-set=latin1
 "The '%s' feature was disabled; you need MySQL built with '%s' to have it working",
 "The MySQL server is running with the %s option so it cannot execute this statement",
 "Column '%-.100s' has duplicated value '%-.64s' in %s"
+"Truncated wrong %-.32s value: '%-.128s'"
diff --git a/sql/share/greek/errmsg.txt b/sql/share/greek/errmsg.txt
index b9f47a54b35..4f695465bf5 100644
--- a/sql/share/greek/errmsg.txt
+++ b/sql/share/greek/errmsg.txt
@@ -295,3 +295,4 @@ character-set=greek
 "The '%s' feature was disabled; you need MySQL built with '%s' to have it working",
 "The MySQL server is running with the %s option so it cannot execute this statement",
 "Column '%-.100s' has duplicated value '%-.64s' in %s"
+"Truncated wrong %-.32s value: '%-.128s'"
diff --git a/sql/share/hungarian/errmsg.txt b/sql/share/hungarian/errmsg.txt
index 9deb3da88a2..fdd48f38ef1 100644
--- a/sql/share/hungarian/errmsg.txt
+++ b/sql/share/hungarian/errmsg.txt
@@ -297,3 +297,4 @@ character-set=latin2
 "The '%s' feature was disabled; you need MySQL built with '%s' to have it working",
 "The MySQL server is running with the %s option so it cannot execute this statement",
 "Column '%-.100s' has duplicated value '%-.64s' in %s"
+"Truncated wrong %-.32s value: '%-.128s'"
diff --git a/sql/share/italian/errmsg.txt b/sql/share/italian/errmsg.txt
index 9b5a081ec9d..b88117c0972 100644
--- a/sql/share/italian/errmsg.txt
+++ b/sql/share/italian/errmsg.txt
@@ -295,3 +295,4 @@ character-set=latin1
 "The '%s' feature was disabled; you need MySQL built with '%s' to have it working",
 "The MySQL server is running with the %s option so it cannot execute this statement",
 "Column '%-.100s' has duplicated value '%-.64s' in %s"
+"Truncated wrong %-.32s value: '%-.128s'"
diff --git a/sql/share/japanese/errmsg.txt b/sql/share/japanese/errmsg.txt
index 1a17277cb90..c1b38e9ecbb 100644
--- a/sql/share/japanese/errmsg.txt
+++ b/sql/share/japanese/errmsg.txt
@@ -297,3 +297,4 @@ character-set=ujis
 "The '%s' feature was disabled; you need MySQL built with '%s' to have it working",
 "The MySQL server is running with the %s option so it cannot execute this statement",
 "Column '%-.100s' has duplicated value '%-.64s' in %s"
+"Truncated wrong %-.32s value: '%-.128s'"
diff --git a/sql/share/korean/errmsg.txt b/sql/share/korean/errmsg.txt
index 9b07afd16c9..932243eca38 100644
--- a/sql/share/korean/errmsg.txt
+++ b/sql/share/korean/errmsg.txt
@@ -295,3 +295,4 @@ character-set=euckr
 "The '%s' feature was disabled; you need MySQL built with '%s' to have it working",
 "The MySQL server is running with the %s option so it cannot execute this statement",
 "Column '%-.100s' has duplicated value '%-.64s' in %s"
+"Truncated wrong %-.32s value: '%-.128s'"
diff --git a/sql/share/norwegian-ny/errmsg.txt b/sql/share/norwegian-ny/errmsg.txt
index aaba0f1afbe..05b55a582bd 100644
--- a/sql/share/norwegian-ny/errmsg.txt
+++ b/sql/share/norwegian-ny/errmsg.txt
@@ -297,3 +297,4 @@ character-set=latin1
 "The '%s' feature was disabled; you need MySQL built with '%s' to have it working",
 "The MySQL server is running with the %s option so it cannot execute this statement",
 "Column '%-.100s' has duplicated value '%-.64s' in %s"
+"Truncated wrong %-.32s value: '%-.128s'"
diff --git a/sql/share/norwegian/errmsg.txt b/sql/share/norwegian/errmsg.txt
index f205e07b3bb..c11e529efd5 100644
--- a/sql/share/norwegian/errmsg.txt
+++ b/sql/share/norwegian/errmsg.txt
@@ -297,3 +297,4 @@ character-set=latin1
 "The '%s' feature was disabled; you need MySQL built with '%s' to have it working",
 "The MySQL server is running with the %s option so it cannot execute this statement",
 "Column '%-.100s' has duplicated value '%-.64s' in %s"
+"Truncated wrong %-.32s value: '%-.128s'"
diff --git a/sql/share/polish/errmsg.txt b/sql/share/polish/errmsg.txt
index 2c942d40f80..319deedc321 100644
--- a/sql/share/polish/errmsg.txt
+++ b/sql/share/polish/errmsg.txt
@@ -299,3 +299,4 @@ character-set=latin2
 "The '%s' feature was disabled; you need MySQL built with '%s' to have it working",
 "The MySQL server is running with the %s option so it cannot execute this statement",
 "Column '%-.100s' has duplicated value '%-.64s' in %s"
+"Truncated wrong %-.32s value: '%-.128s'"
diff --git a/sql/share/portuguese/errmsg.txt b/sql/share/portuguese/errmsg.txt
index f3a8a484696..5151e612813 100644
--- a/sql/share/portuguese/errmsg.txt
+++ b/sql/share/portuguese/errmsg.txt
@@ -296,3 +296,4 @@ character-set=latin1
 "The '%s' feature was disabled; you need MySQL built with '%s' to have it working",
 "The MySQL server is running with the %s option so it cannot execute this statement",
 "Column '%-.100s' has duplicated value '%-.64s' in %s"
+"Truncated wrong %-.32s value: '%-.128s'"
diff --git a/sql/share/romanian/errmsg.txt b/sql/share/romanian/errmsg.txt
index 72df2447f99..2c6ec81cc1d 100644
--- a/sql/share/romanian/errmsg.txt
+++ b/sql/share/romanian/errmsg.txt
@@ -299,3 +299,4 @@ character-set=latin2
 "The '%s' feature was disabled; you need MySQL built with '%s' to have it working",
 "The MySQL server is running with the %s option so it cannot execute this statement",
 "Column '%-.100s' has duplicated value '%-.64s' in %s"
+"Truncated wrong %-.32s value: '%-.128s'"
diff --git a/sql/share/russian/errmsg.txt b/sql/share/russian/errmsg.txt
index 3d37b2d60ce..00e931eb65a 100644
--- a/sql/share/russian/errmsg.txt
+++ b/sql/share/russian/errmsg.txt
@@ -297,3 +297,4 @@ character-set=koi8r
 "The '%s' feature was disabled; you need MySQL built with '%s' to have it working",
 "The MySQL server is running with the %s option so it cannot execute this statement",
 "Column '%-.100s' has duplicated value '%-.64s' in %s"
+"Truncated wrong %-.32s value: '%-.128s'"
diff --git a/sql/share/serbian/errmsg.txt b/sql/share/serbian/errmsg.txt
index c68f9538dd2..dc15bbf4977 100644
--- a/sql/share/serbian/errmsg.txt
+++ b/sql/share/serbian/errmsg.txt
@@ -289,3 +289,4 @@ character-set=cp1250
 "The '%s' feature was disabled; you need MySQL built with '%s' to have it working"
 "The MySQL server is running with the %s option so it cannot execute this statement"
 "Column '%-.100s' has duplicated value '%-.64s' in %s"
+"Truncated wrong %-.32s value: '%-.128s'"
diff --git a/sql/share/slovak/errmsg.txt b/sql/share/slovak/errmsg.txt
index 35263024bb8..00b0dd9a0ae 100644
--- a/sql/share/slovak/errmsg.txt
+++ b/sql/share/slovak/errmsg.txt
@@ -303,3 +303,4 @@ character-set=latin2
 "The '%s' feature was disabled; you need MySQL built with '%s' to have it working",
 "The MySQL server is running with the %s option so it cannot execute this statement",
 "Column '%-.100s' has duplicated value '%-.64s' in %s"
+"Truncated wrong %-.32s value: '%-.128s'"
diff --git a/sql/share/spanish/errmsg.txt b/sql/share/spanish/errmsg.txt
index 3e0f67b453a..cb5d5b6df28 100644
--- a/sql/share/spanish/errmsg.txt
+++ b/sql/share/spanish/errmsg.txt
@@ -297,3 +297,4 @@ character-set=latin1
 "The '%s' feature was disabled; you need MySQL built with '%s' to have it working",
 "The MySQL server is running with the %s option so it cannot execute this statement",
 "Column '%-.100s' has duplicated value '%-.64s' in %s"
+"Truncated wrong %-.32s value: '%-.128s'"
diff --git a/sql/share/swedish/errmsg.txt b/sql/share/swedish/errmsg.txt
index dc6759f91e0..5c60519e217 100644
--- a/sql/share/swedish/errmsg.txt
+++ b/sql/share/swedish/errmsg.txt
@@ -295,3 +295,4 @@ character-set=latin1
 "MySQL är started i --skip-grant-tables mod. Pga av detta kan du inte använda detta program",
 
 "Column '%-.100s' has duplicated value '%-.64s' in %s"
+"Truncated wrong %-.32s value: '%-.128s'"
diff --git a/sql/share/ukrainian/errmsg.txt b/sql/share/ukrainian/errmsg.txt
index fbf31744dca..7dd694e75ac 100644
--- a/sql/share/ukrainian/errmsg.txt
+++ b/sql/share/ukrainian/errmsg.txt
@@ -300,3 +300,4 @@ character-set=koi8u
 "The '%s' feature was disabled; you need MySQL built with '%s' to have it working",
 "The MySQL server is running with the %s option so it cannot execute this statement",
 "Column '%-.100s' has duplicated value '%-.64s' in %s"
+"Truncated wrong %-.32s value: '%-.128s'"
diff --git a/sql/time.cc b/sql/time.cc
index 1dff0c62edf..376ad6926b8 100644
--- a/sql/time.cc
+++ b/sql/time.cc
@@ -391,9 +391,11 @@ str_to_TIME(const char *str, uint length, TIME *l_time, uint flags)
   ulong not_zero_date, allow_space;
   bool is_internal_format;
   const char *pos, *last_field_pos;
+  const char *str_begin= str;
   const char *end=str+length;
   const uchar *format_position;
   bool found_delimitier= 0, found_space= 0;
+  uint frac_pos, frac_len;
   DBUG_ENTER("str_to_TIME");
   DBUG_PRINT("ENTER",("str: %.*s",length,str));
 
@@ -482,7 +484,7 @@ str_to_TIME(const char *str, uint length, TIME *l_time, uint flags)
       tmp_value=tmp_value*10 + (ulong) (uchar) (*str - '0');
       str++;
     }
-    date_len[i]+= (uint) (str - start);
+    date_len[i]= (uint) (str - start);
     if (tmp_value > 999999)			// Impossible date part
       DBUG_RETURN(TIMESTAMP_NONE);
     date[i]=tmp_value;
@@ -535,9 +537,9 @@ str_to_TIME(const char *str, uint length, TIME *l_time, uint flags)
       {
 	if (str+2 <= end && (str[1] == 'M' || str[1] == 'm'))
 	{
-	  if (str[1] == 'p' || str[1] == 'P')
+	  if (str[0] == 'p' || str[0] == 'P')
 	    add_hours= 12;
-	  else if (str[1] != 'a' || str[1] != 'A')
+	  else if (str[0] != 'a' || str[0] != 'A')
 	    continue;				// Not AM/PM
 	  str+= 2;				// Skip AM/PM
 	  /* Skip space after AM/PM */
@@ -569,7 +571,13 @@ str_to_TIME(const char *str, uint length, TIME *l_time, uint flags)
     l_time->hour=		date[(uint) format_position[3]];
     l_time->minute=		date[(uint) format_position[4]];
     l_time->second=		date[(uint) format_position[5]];
-    l_time->second_part=	date[(uint) format_position[6]];
+
+    frac_pos= (uint) format_position[6];
+    frac_len= date_len[frac_pos];
+    if (frac_len < 6)
+      date[frac_pos]*= (uint) log_10_int[6 - frac_len];
+    l_time->second_part= date[frac_pos];
+
     if (format_position[7] != (uchar) 255)
     {
       if (l_time->hour > 12)
@@ -585,6 +593,8 @@ str_to_TIME(const char *str, uint length, TIME *l_time, uint flags)
     l_time->hour=	date[3];
     l_time->minute=	date[4];
     l_time->second=	date[5];
+    if (date_len[6] < 6)
+      date[6]*= (uint) log_10_int[6 - date_len[6]];
     l_time->second_part=date[6];
   }
   l_time->neg= 0;
@@ -614,15 +624,17 @@ str_to_TIME(const char *str, uint length, TIME *l_time, uint flags)
       current_thd->cuted_fields++;
     goto err;
   }
-  if (str != end && current_thd->count_cuted_fields)
+
+  l_time->time_type= (number_of_fields <= 3 ? 
+		      TIMESTAMP_DATE : TIMESTAMP_DATETIME);
+
+  for (; str != end ; str++)
   {
-    for (; str != end ; str++)
+    if (!my_isspace(&my_charset_latin1,*str))
     {
-      if (!my_isspace(&my_charset_latin1,*str))
-      {
-	current_thd->cuted_fields++;
-	break;
-      }
+      make_truncated_value_warning(current_thd, str_begin, length,
+				   l_time->time_type);
+      break;
     }
   }
 
@@ -686,6 +698,7 @@ bool str_to_time(const char *str,uint length,TIME *l_time)
 {
   long date[5],value;
   const char *end=str+length, *end_of_days;
+  const char *str_begin= str;
   bool found_days,found_hours;
   uint state;
 
@@ -706,7 +719,7 @@ bool str_to_time(const char *str,uint length,TIME *l_time)
   {						// Probably full timestamp
     enum timestamp_type res= str_to_TIME(str,length,l_time,
 					 (TIME_FUZZY_DATE |
-					 TIME_DATETIME_ONLY));
+					  TIME_DATETIME_ONLY));
     if ((int) res >= (int) TIMESTAMP_DATETIME_ERROR)
       return res == TIMESTAMP_DATETIME_ERROR;
   }
@@ -784,6 +797,8 @@ fractional:
            my_isdigit(&my_charset_latin1,str[0]) && 
            field_length--)
       value=value*10 + (uint) (uchar) (*str - '0');
+    if (field_length)
+      value*= (long) log_10_int[field_length];
     date[4]=value;
   }
   else
@@ -796,12 +811,12 @@ fractional:
       str++;
     if (str+2 <= end && (str[1] == 'M' || str[1] == 'm'))
     {
-      if (str[1] == 'p' || str[1] == 'P')
+      if (str[0] == 'p' || str[0] == 'P')
       {
 	str+= 2;
 	date[1]= date[1]%12 + 12;
       }
-      else if (str[1] == 'a' || str[1] == 'A')
+      else if (str[0] == 'a' || str[0] == 'A')
 	str+=2;
     }
   }
@@ -822,13 +837,14 @@ fractional:
   l_time->time_type= TIMESTAMP_TIME;
 
   /* Check if there is garbage at end of the TIME specification */
-  if (str != end && current_thd->count_cuted_fields)
+  if (str != end)
   {
     do
     {
       if (!my_isspace(&my_charset_latin1,*str))
       {
-	current_thd->cuted_fields++;
+	make_truncated_value_warning(current_thd, str_begin, length,
+				     TIMESTAMP_TIME);
 	break;
       }
     } while (++str != end);
@@ -1265,3 +1281,35 @@ void make_datetime(DATE_TIME_FORMAT *format, TIME *l_time, String *str)
   str->length(length);
   str->set_charset(&my_charset_bin);
 }
+
+void make_truncated_value_warning(THD *thd, const char *str_val,
+				  uint str_length, timestamp_type time_type)
+{
+  char warn_buff[MYSQL_ERRMSG_SIZE];
+  const char *type_str;
+
+  char buff[128];
+  String str(buff,(uint32) sizeof(buff), system_charset_info);
+  str.length(0);
+  str.append(str_val, str_length);
+  str.append('\0');
+
+  switch (time_type) {
+    case TIMESTAMP_DATE: 
+      type_str= "date";
+      break;
+    case TIMESTAMP_DATETIME:
+      type_str= "datetime";
+      break;
+    case TIMESTAMP_TIME:
+      type_str= "time";
+      break;
+    default:
+      type_str= "string";
+      break;
+  }
+  sprintf(warn_buff, ER(ER_TRUNCATED_WRONG_VALUE),
+	  type_str, str.ptr());
+  push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
+		      ER_TRUNCATED_WRONG_VALUE, warn_buff);
+}