Conversion Between Date and Time Types 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: 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  DATETIME  and  TIMESTAMP  values: 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’ . 2033 •Conversion to a  TIME  value discards the date part because the  TIME  type contains no date information. •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 is not useful; the result is  ’00:00:00’ . •Conversion to a  DATETIME  or  TIMESTAMP  value adds a time part of  ’00:00:00’  because the  DATE value contains no time information. Conversion of  DATE  values: 11.2.7 Conversion Between Date and Time Types •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 •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. +-------------+------------------------+------------------------+ | 17:51:04.78 | 2018-09-08 17:51:04.78 | 2018-09-08 17:51:04.78 | +-------------+------------------------+------------------------+ 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 | +-------------+------------------------+------------------------+