Date in Excel from SQL

Question

I have an excel created from a comma-delimited text file originally from a .sql file with an SQL INSERT query.

In one of the columns I have: "Cast(0x123456AB...) As TIME Obviously this is NOT the jsondate format... so no help from that question...

I replaced the Cast( and replaced the ") As TIME" with empty strings.

So now I have the time values in hexadecimal.

How do I convert them into Excel Time or Datetime?

Obviously this is NOT javadate, but maybe its similar. Will play around with that. — pashute, Nov 21 '16 at 19:24
`=HEX2DEC(Cell)` and format the cell/column as date. this will convert to decimal and formatting it as date will take the decimal to date. — xQbert, Nov 21 '16 at 19:28

score 0 · Accepted Answer · edited May 23 '17 at 11:45

OK Playing around with it showed me that it's exactly the same as the jquery date answer. You take the numeric portion starting with 0x.

Take the 10 digits AFTER the 0x. e.g. in A2: =MID(A1, 3, 10)
Turn it into hexadecimal e.g. in A3: = HEX2DEC(A2)
Divide by 86400 e.g. A4: =A3/86400
And add the result to 1/1/1970 date. e.g. = A5: =A4 + Date(1970, 1, 1)

Or in short:

=(hex2dec(mid(a1,numstart,10))/86400) + date(1970,1,1)

Replace numstart with the 1-starting index of the number.

e.g. 3 if you have a 12 or 13 digit number like 0x12345678AB and you'll get 12345678AB

This is similar to the Convert JSON Date /Date(1388624400000)/ to Date in Excel

Except that:

a. The question was answered wrong and wouldn't work. (I edited it)

b. The .sql file was retrieved in a stored procedure from the database via SQL. While in the question they were using jquery returned ajax data, which seemed to differ. Turns out they're the same number with a different format.

As an added remark, I had a space mark at the beginning of my hex number. Until I did the MID on it, I didn't see that.

Note: When using ajax returned formatted dates like /date:0x12345678ab/ you'll set numstart to 8. If hex2dec fails, try turning the hex string into uppercase before calling hex2dec. To debug just put each formula in a separate cell, so you see what works and what doesn't.

Date in Excel from SQL

1 Answers1