+-------------+------------------------+------------------------+ | 17:51:04.78 | 2018-09-08 17:51:04.78 | 2018-09-08 17:51:04.78 | +-------------+------------------------+------------------------+ 

No warning or error is given when such rounding occurs. This behavior follows the SQL standard. 

To insert the values with truncation instead, enable the TIME_TRUNCATE_FRACTIONAL  SQL mode: 

SET @@sql_mode = sys.list_add(@@sql_mode, ’TIME_TRUNCATE_FRACTIONAL’); 

With that SQL mode enabled, the temporal values are inserted with truncation: 

mysql> SELECT * FROM fractest; +-------------+------------------------+------------------------+ | c1 | c2 | c3 | +-------------+------------------------+------------------------+ | 17:51:04.77 | 2018-09-08 17:51:04.77 | 2018-09-08 17:51:04.77 | +-------------+------------------------+------------------------+ 

•Functions that take temporal arguments accept values with fractional seconds. Return values from temporal functions include fractional seconds as appropriate. For example, NOW()  with no argument returns the current date and time with no fractional part, but takes an optional argument from 0 to 6 to specify that the return value includes a fractional seconds part of that many digits. 

•Syntax for temporal literals produces temporal values: DATE ’ str ’ , TIME ’ str ’ , and TIMESTAMP ’ str ’ , and the ODBC-syntax equivalents. The resulting value includes a trailing fractional seconds part if specified. Previously, the temporal type keyword was ignored and these constructs produced the string value. See Standard SQL and ODBC Date and Time Literals 

## 11.2.7 Conversion Between Date and Time Types 

To some extent, you can convert a value from one temporal type to another. However, there may be some alteration of the value or loss of information. In all cases, conversion between temporal types is subject to the range of valid values for the resulting type. For example, although DATE , DATETIME , and TIMESTAMP values all can be specified using the same set of formats, the types do not all have the same range of values. TIMESTAMP  values cannot be earlier than 1970  UTC or later than ’2038-01-19 03:14:07’ UTC. This means that a date such as ’1968-01-01’ , while valid as a DATE  or DATETIME  value, is not valid as a TIMESTAMP  value and is converted to 0 . 

Conversion of DATE  values: 

•Conversion to a DATETIME  or TIMESTAMP  value adds a time part of ’00:00:00’  because the DATE value contains no time information. 

•Conversion to a TIME  value is not useful; the result is ’00:00:00’ . 

Conversion of DATETIME  and TIMESTAMP  values: 

•Conversion to a DATE  value takes fractional seconds into account and rounds the time part. For example, ’1999-12-31 23:59:59.499’  becomes ’1999-12-31’ , whereas ’1999-12-31 23:59:59.500’  becomes ’2000-01-01’ . 

•Conversion to a TIME  value discards the date part because the TIME  type contains no date information. 

For conversion of TIME  values to other temporal types, the value of CURRENT_DATE()  is used for the date part. The TIME  is interpreted as elapsed time (not time of day) and added to the date. This means that the date part of the result differs from the current date if the time value is outside the range from ’00:00:00’ to ’23:59:59’ . 

