Microsoft Hex dates

I have the following from开发者_开发技巧 a Microsoft SQL Server database for date/time value:

0x00009CEF00A25634

I found this post: Help me translate long value, expressed in hex, back in to a date/time

Which seemed to be on the right track but by using the code I didn't get the right dates, are my hex dates in a different format? How would I convert them to a normal date, I am using PHP/PostgreSQL.

select CAST (0x00009CEF00A25634 as datetime) gives 2009-12-30 09:51:03.000

This is two integers. One for the date part 0x00009CEF (decimal 40175) and one for the time part 00A25634 (decimal 10638900). The date part is a signed integer giving number of days since 1 Jan 1900. The time part is an integer representing number of ticks.

There are 300 ticks in a second.

It can be seen that the following also returns the same result

SELECT DATEADD(MILLISECOND,10638900*10/3.0, DATEADD(DAY,40175, '19000101'))

You will need to figure out how to apply this to postgres.

Edit: an answer here apparently does this. I haven't tested it myself.

This works for me while migrating from SQL to MySQL :

SELECT (CAST('1900-01-01 00:00:00' + INTERVAL CAST(CONV(substr(HEX( 0x0000A249004576D0 ),1,8), 16, 10) AS SIGNED) DAY + INTERVAL CAST(CONV(substr(HEX( 0x0000A249004576D0 ),9,8), 16, 10) AS SIGNED)* 10000/3 MICROSECOND AS DATETIME)) AS newdate