how to cast the hexadecimal to varchar(datetime)?

by

That looks like the SQL Server datetime format. Internally this is stored as 2 integers with the first 4 bytes being the days since 1st jan 1900 and the 2nd being the number of ticks since midnight (each tick being 1/300 of a second).

If you need to use this in MySQL you could do 

SELECT 
      CAST(
          '1900-01-01 00:00:00' + 
          INTERVAL CAST(CONV(substr(HEX(BinaryData),1,8), 16, 10)  AS SIGNED) DAY +
          INTERVAL CAST(CONV(substr(HEX(BinaryData),9,8), 16, 10)  AS SIGNED)* 10000/3 MICROSECOND
      AS DATETIME) AS converted_datetime
FROM
(
SELECT 0x0000987C00000000 AS BinaryData
UNION ALL
SELECT 0x00009E85013711EE AS BinaryData
) d

Returns

converted_datetime
--------------------------
2006-11-17 00:00:00
2011-02-09 18:52:34.286667

(Thanks to Ted Hopp for the solution in splitting the binary data)

More Related Contents:

  1. SQL: Repeat a result row multiple times, and number the rows
  2. Why do results from a SQL query not come back in the order I expect?
  3. Best practices for SQL varchar column length [closed]
  4. SQL Query needed for fetch value from Two identical table but not fully identical table
  5. sql query to modify the selected value from database [closed]
  6. Calculate Average of the largest values in multiple columns
  7. SQL Print the name of all Employees together with the name of their supervisor
  8. How to select the nth row in a SQL database table?
  9. MySQL Query GROUP BY day / month / year
  10. Group by clause in mySQL and postgreSQL, why the error in postgreSQL?
  11. Error in MySQL when setting default value for DATE or DATETIME
  12. Storing JSON in database vs. having a new column for each key
  13. Difference between two dates in MySQL
  14. MySQL Query to select data from last week?
  15. Datetime equal or greater than today in MySQL
  16. Set NOW() as Default Value for datetime datatype?
  17. How do I decide when to use right joins/left joins or inner joins Or how to determine which table is on which side?
  18. Group mysql query by 15 min intervals
  19. SQL Query to show nearest date?
  20. Relationship between catalog, schema, user, and database instance
  21. What resources exist for Database performance-tuning? [closed]
  22. How to transpose rows to columns with large amount of the data in BigQuery/SQL?
  23. Are SELECT type queries the only type that can be nested?
  24. Postgres LEFT JOIN with WHERE condition
  25. Get total hours worked in a day mysql
  26. MySQL: Insert datetime into other datetime field
  27. Equivalent of MySQL ON DUPLICATE KEY UPDATE in Sql Server
  28. What are the max number of allowable parameters per database provider type?
  29. What happens when I exhaust a bigint generated key? How to handle it?
  30. Why in SQL NULL can’t match with NULL?

Leave a Comment