CAST Hex Value for Datetime - SQL Server to MySQL Migration

In SQL Server, you can use CAST function with a hexadecimal value to convert it to a DATETIME value. In MySQL, you have to use an interval expression (see below):

SQL Server:

  SELECT CAST(0x0000A074004308F0 AS DATETIME);
  # 2012-06-18 04:04:04.000

When a hex literal is specified in CAST AS DATE function, SQL Server uses the first 2 bytes (0000A074 as in example) as the number of days since 1900-01-01, and subsequent 2 bytes (004308F0 as in example) as the number of ticks (1/300 of second) since the midnight.

You can use the following expression to get the same result in MySQL:

MySQL:

  SELECT CAST('1900-01-01' + INTERVAL 0x0000A074 DAY + INTERVAL 0x004308F0/300 SECOND AS DateTime(3));
  # 2012-06-18 04:04:04.000

For more information, see SQL Server to MySQL Migration