0

I have code for hexadecimal CAST(0x0000A2F5016C1769 AS DateTime) to mysql datetime, from how to cast the hexadecimal to varchar(datetime)?

code:

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

But i need to convert CAST date CAST(0xA2380B00 AS Date) to varchar date

Is any possible?

Community
  • 1
  • 1
Dhamu
  • 1,694
  • 5
  • 22
  • 47

1 Answers1

0

Well, i don't really understand why you need to convert it to varchar, because if i'm not mistaken datetime behaving like string in most if not all of the situations. However, you can use the DATE_FORMAT function. It outputs string, and you can format the datetime if you want.

Derenir
  • 537
  • 1
  • 12
  • 29
  • For inserting into mysql DB – Dhamu Dec 11 '14 at 11:08
  • So something like this? `SELECT CAST( CAST( '1900-01-01' + INTERVAL CAST(CONV(substr(HEX(BinaryData),1,8), 16, 10) AS SIGNED) DAY + INTERVAL CAST(CONV(substr(HEX(BinaryData),9,8),16,10) AS SIGNED)* 10000/3 MICROSECOND AS DATE) AS CHAR ) AS converted_date FROM ( SELECT 0x0000A34900BD693D AS BinaryData ) d;` – Derenir Dec 11 '14 at 12:15
  • date - 0xA2380B00... result- NULL – Dhamu Dec 15 '14 at 20:12