2

I have a problem with processing a SQL Server database dump in PHP.

I have one column called datatime with values like : 

0x0000a0af00d7f2eb

I need to extract, in PHP, the date and time values of this column. I don't have SQL Server available so I can't use the obvious solution of CAST(0x0000a0af00d7f2eb AS datetime).

Someone has told me that this hex: 0000a0af00d7f2eb is created by 4 bytes of date and 4 bytes of time.

So I know that:

When I will change 0000a0af (first 4 bytes) to decimal I will get number of days from 1900. That works fine.

But when I'm trying to change last the 4 bytes (so there should be time) : 00d7f2eb to decimal I'm getting something which I can't understand. It should be a time from midnight in milliseconds and sometimes this value is ~3 times lower.

Could anyone help in converting 0000a0af00d7f2eb to date? I know that time is between 5 AM and 11 PM, and the day is in last week.

dsolimano
  • 8,870
  • 3
  • 48
  • 63
user1611089
  • 33
  • 7
  • did u try select CAST(0x0000a0af00d7f2eb AS datetime) ? it gives the output 2012-08-16 13:06:14.757, is that what you are looking for? – Joe G Joseph Aug 20 '12 at 07:20
  • 1
    Maybe not a full duplicate but might also help: http://stackoverflow.com/questions/1143259/what-is-the-internal-representation-of-datetime-in-sql-server – rene Aug 20 '12 at 07:40
  • @rene actually, i think this is an exact duplicate: OP needs to know internal representation of datetime so that they can convert without using SQL Server – Dancrumb Aug 20 '12 at 14:42

2 Answers2

2

According to the linked article in the other question linked to by Rene, SQL Server stores 3.33 millisecond intervals in the second set of 4 bytes, not milliseconds. So if you're calculating with milliseconds, you will indeed be getting a time about 1/3 of what it should be. Using your example, let's start by converting to decimal

00d7f2eb -> 14152427 3.3ms intervals

Now multiply out by 3.3 to convert to milliseconds, and divide by 1000 to get seconds

14152427 * 3.3 / 1000 ~ 47127.58

So this represents about 47 thousand seconds after midnight. Dividing by 3600 seconds in an hour

47127.58 / 3600 ~ 13.091

So this represents a time of about 13.1 hours after midnight, which agrees with the result of the cast done in SQL Server.

dsolimano
  • 8,870
  • 3
  • 48
  • 63
0
select CAST(0x0000a0af00d7f2eb AS datetime) as t

is working fine for me. and it returns 'August, 16 2012 13:06:14-0700'.

Pradeeshnarayan
  • 1,235
  • 10
  • 21
  • If you are not getting this or expecting some other result please provide more information(server version or sample hex value and corresponding date). – Pradeeshnarayan Aug 20 '12 at 10:08
  • Yes, it's working great, but I need to do it on php script ( I have var "CAST(0x..... AS datetime) in PHP and I need to get date. I don't have mssql server so I can't make select. – user1611089 Aug 20 '12 at 12:54