Microsoft Hex dates

Question

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.

Answer 1

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.