I have the datetime exporting is "CAST(0x0000987C00000000 AS DateTime)" but when I want to get it back into datetime.It is a NULL value. how can i get it to datetime again.
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
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)