MySQL vs SQL Server DATETIME: precision, time zones, and TIMESTAMP traps
Copyright Notice: This article is an original work licensed under the CC 4.0 BY-NC-ND license.
If you wish to repost this article, please include the original source link and this copyright notice.
Source link: https://v2know.com/article/498
MySQL DATETIME and SQL Server datetime are not interchangeable. Both represent a date and time without retaining a time-zone offset, but their supported ranges and fractional-second precision differ. For new SQL Server tables, datetime2 is usually the more useful comparison.
The examples below use the MySQL 8.4 manual and Microsoft's SQL Server documentation. A column becoming varchar during migration is a mapping choice made by a tool or driver; it is not an inherent rule of either datetime type.
MySQL DATETIME vs SQL Server datetime and datetime2
| Type | Supported calendar dates | Fractional seconds | Retains a UTC offset? |
|---|---|---|---|
MySQL DATETIME(p) | 1000-01-01 to 9999-12-31 | 0–6 digits; p defaults to 0 | No |
SQL Server datetime | 1753-01-01 to 9999-12-31 | About 3.33 ms increments, displayed as .000, .003, .007, etc. | No |
SQL Server datetime2(p) | 0001-01-01 to 9999-12-31 | 0–7 digits; p defaults to 7 | No |
The ranges are documented in MySQL's date and time types, Microsoft's datetime reference, and its datetime2 reference. These are supported ranges for valid dates, not a recommendation to use MySQL zero dates as missing values.
Precision: datetime is not the same as datetime2(3)
SQL Server's older datetime rounds fractional seconds to its supported increments. Three displayed decimal places do not mean that every millisecond is representable:
-- SQL Server
SELECT
CONVERT(varchar(23),
CAST('2026-10-17T12:00:00.004' AS datetime), 121) AS old_datetime,
CAST('2026-10-17T12:00:00.004' AS datetime2(6)) AS new_datetime;
-- Expected values:
-- old_datetime: 2026-10-17 12:00:00.003
-- new_datetime: 2026-10-17 12:00:00.004000
In MySQL, specify the precision explicitly when fractions matter:
-- MySQL 8.4
SELECT CAST('2026-10-17 12:00:00.123456' AS DATETIME(6)) AS precise_value;
-- Expected value: 2026-10-17 12:00:00.123456
DATETIME without a precision means DATETIME(0). The MySQL fractional-seconds documentation also explains rounding when reducing precision and the optional TIME_TRUNCATE_FRACTIONAL SQL mode. Make the intended policy explicit when migrating values; a successful import can still lose fractional digits.
A practical cross-database choice is SQL Server datetime2(6) and MySQL DATETIME(6), provided the dates fit both ranges and the application assigns the same meaning to them. This does not make their SQL syntax or time-zone handling identical.
MySQL DATETIME vs TIMESTAMP: session time zones matter
MySQL TIMESTAMP converts between the connection's session time zone and UTC: to UTC when storing, and back to the current session time zone when reading. A stored DATETIME does not receive that automatic conversion when the session time zone changes. See MySQL's time-zone support.
This example inserts the same clock reading into both types while the session is in UTC, then reads it in UTC+09:00. It uses a temporary table and restores the original session setting:
-- MySQL: run this in a separate test connection.
SET @saved_time_zone_498 = @@SESSION.time_zone;
SET SESSION time_zone = '+00:00';
CREATE TEMPORARY TABLE date_type_demo_498 (
d DATETIME(6),
t TIMESTAMP(6)
);
INSERT INTO date_type_demo_498 (d, t)
VALUES ('2026-10-17 12:00:00.123456', '2026-10-17 12:00:00.123456');
SET SESSION time_zone = '+09:00';
SELECT d, t FROM date_type_demo_498;
-- d: 2026-10-17 12:00:00.123456
-- t: 2026-10-17 21:00:00.123456
DROP TEMPORARY TABLE date_type_demo_498;
SET SESSION time_zone = @saved_time_zone_498;
The different TIMESTAMP display represents the same instant. Neither column retains the original zone name or the original offset. TIMESTAMP therefore does not eliminate the need to configure every application connection consistently.
MySQL 8.4's TIMESTAMP range runs from January 1970 to January 2038, with exact UTC boundaries given in the type reference. Check those limits before choosing it for future bookings or historical data. DATETIME has a much wider calendar range.
Three different meanings of “timestamp”
- MySQL TIMESTAMP: a temporal column type with session time-zone conversion. It is not a column containing a 10-digit or 13-digit decimal number.
- Unix timestamp: a numeric count from an epoch, often expressed in seconds or milliseconds. State the unit in the API contract or column name; the number of digits alone is not a type definition.
- SQL Server timestamp: a synonym for
rowversion, an automatically generated 8-byte value used for row versioning. It does not store a date or time.
Microsoft recommends the rowversion name because the SQL Server timestamp syntax is deprecated. Do not map it to a MySQL TIMESTAMP column merely because the names match; preserving concurrency behavior requires a separate design.
What about datetimeoffset and UTC?
SQL Server datetimeoffset retains a UTC offset, unlike datetime and datetime2. An offset such as +09:00 is not a named time zone with daylight-saving rules. MySQL DATETIME and TIMESTAMP have no equivalent field that preserves that offset alongside the value.
For event instants, an application can normalize to UTC and store the result in a clearly defined UTC DATETIME column, or use MySQL TIMESTAMP within its range. With DATETIME, the application must enforce the UTC convention. Keep the original offset separately if it is required, and keep a named time zone when future local scheduling depends on regional rules.
Migration checks that prevent silent data changes
- Define the meaning first. Is the value a local appointment time, a UTC instant, or a version token? Similar-looking strings can represent different things.
- Check the actual range. SQL Server datetime2 permits years before 1000 that MySQL DATETIME does not support. MySQL DATETIME permits dates before SQL Server datetime's 1753 lower bound.
- Choose precision explicitly. Moving datetime2(7) into DATETIME(6) cannot preserve every possible fractional value. Decide how to round or truncate before import.
- Review generated schema and connection settings. Verify destination column types, parameter types, session time zones, SQL modes and NULL handling instead of replacing every date column with TIMESTAMP.
- Compare representative rows. Include fractions, boundary dates, NULLs and values read under two session time zones. Compare both the intended instant and the displayed clock reading.
This article was last edited at