How to parse SQL Server binary date format in mysql?

Question

How to convert CAST(0xE3350B00 AS DATE) to mysql ?

I used various forum supports and have following results

0xE3350B00 => 2059-04-03 22:56
0x0000A17F00000000 => 2013-03-12

But both dates belong to same line of data so i am confident that 0xE3350B00 should convert to nearby 2013-03-12 but not finding it technically? Can anyone help if conversion of 0xE3350B00 date??

I used following code in sql function:

return date_add(date_add("1900-01-01 00:00:00", interval conv(substr(HEX(raw_data), 5, 4), 16, 10) DAY), interval conv(substr(HEX(raw_data), 1, 4), 16, 10) MINUTE);

Removed `Sql Server` which is irrelevant to current question — Pரதீப், Jan 03 '16 at 07:26
@MartinSmith I didnt find anything related to sql server in question except your assumption — Pரதீப், Jan 03 '16 at 08:35
@VR46 - The tag was a pretty big clue that you removed. With that context and actually trying `SELECT CAST(0xE3350B00 AS DATE), CAST(0x0000A17F00000000 AS DATETIME)` it is clear these are binary values exported from SQL Server. — Martin Smith, Jan 03 '16 at 08:38
actually i added that tag with same line of thoughts of @martinsmith — Abhinav Bhardwaj, Jan 03 '16 at 14:02

score 0 · Accepted Answer · edited May 23 '17 at 12:15

0

The code you have looks as though it parses SQL Server datetime values (though incorrectly if they have any time part).

This looks like a SQL Server binary date format.

These are stored differently. They occupy three bytes and represents the number of days since 0001-01-01 as a byte reversed integer.

You can use

SELECT 
      CAST(
          '0001-01-01 00:00:00' + 
          INTERVAL CAST(CONV(
                        CONCAT(substr(HEX(BinaryData),5,2),
                               substr(HEX(BinaryData),3,2),
                               substr(HEX(BinaryData),1,2))
                        , 16, 10)  AS SIGNED) DAY 
      AS DATE) AS converted_date
FROM
(
SELECT 0xE3350B00 AS BinaryData
) d

Which evaluates to 2012-07-08.

SQL Fiddle

edited May 23 '17 at 12:15

Community

1
1

answered Jan 03 '16 at 08:28

Martin Smith

438,706
87
741
845

Thank Martin... Great help for me. It would give more confident if you can verify that following conversion is right which i did 0x0000A17F00000000 => 2013-03-12 – Abhinav Bhardwaj Jan 03 '16 at 13:58
@AbhinavBhardwaj - That's correct for a SQL Server `datetime` - but the code in your question will only work correctly if the time part is zero. The integer in the time portion is `1/300` of a second. Not minutes. See http://stackoverflow.com/questions/4946292/how-to-cast-the-hexadecimal-to-varchardatetime/4946759#4946759 for correct code. – Martin Smith Jan 03 '16 at 14:02
I am using following for datetime: return CAST( '1900-01-01 00:00:00' + INTERVAL CAST(CONV(substr(HEX(dttm),1,8), 16, 10) AS SIGNED) DAY + INTERVAL CAST(CONV(substr(HEX(dttm),9,8), 16, 10) AS SIGNED)* 10000/3 MICROSECOND AS DATETIME); – Abhinav Bhardwaj Jan 03 '16 at 14:06
Thanks a lot for solving it! – Abhinav Bhardwaj Jan 03 '16 at 14:11

How to parse SQL Server binary date format in mysql?

1 Answers1