CAST(0x993902CE AS DATETIME) FROM SQL SERVER TO MYSQL

Question

Does anyone knows how to CAST(0x993902CE AS DATETIME) from SQL SERVER (smalldatetime binary format) to MYSQL?

I tried CAST(0X993902CE AS DATETIME) in mysql but it returns NULL when it should return '2007-05-25 11:58:00'.

If anyone can tell me a way to do this, I'd be grateful.

If I'm not mistaken, `0x993902CE` is a UNIX timestamp? Try `FROM_UNIXTIME(0x993902CE)` instead. I cannot test it at the moment, so I won't submit it as an answer. — Vincent Savard, Oct 24 '12 at 18:13
@VincentSavard How can I use FROM_UNIXTIME(0x993902CE) in MYSQL syntax? — avolquez, Oct 24 '12 at 18:17
@evolquez: It's the `FROM_UNIXTIME` function, but from my tests it doesn't return what you're looking for, so I wouldn't expect it to work. I'm not sure what the number `0x993902CE` represents, actually. — Vincent Savard, Oct 24 '12 at 18:19
@MartinSmith You are right, but what I try is convert or cast it to DATETIME in MYSQL — avolquez, Oct 24 '12 at 18:19
@VincentSavard :If I try 'CAST(0x993902CE AS SmallDateTime)' in SQL-SERVER it should return '2007-07-02 00:00:00', I need to do the same but using MYSQL. — avolquez, Oct 24 '12 at 18:22
@evolquez: Yes, I understood what you wanted to do. That doesn't explain, however, what the number is supposed to be. — Vincent Savard, Oct 24 '12 at 18:24
`SELECT CAST(0x9939 AS INT) , CAST(0x02CE AS INT)` gives `39225` and `718` which is days after January 1, 1900 and number of minutes since midnight respectively.` [My answer to a similar question could be used as a start](http://stackoverflow.com/questions/4946292/how-to-cast-the-hexadecimal-to-varchardatetime/4946759#4946759) — Martin Smith, Oct 24 '12 at 18:24
@VincentSavard: using SQLS-SERVER as SmallDateTime sintax it returns '2007-07-02 00:00:00' — avolquez, Oct 24 '12 at 18:29
@MartinSmith: You're absolutely right, it returns 2007-05-25 11:58:00, excuse me — avolquez, Oct 24 '12 at 18:34

Andreas Wederbrand · Accepted Answer · 2012-10-24T19:01:40.383

Following @MartinSmith's earlier suggestion in the comments and solution I wrote this simple code

SELECT "0x993902CE" INTO @raw_data;

SELECT conv(substr(@raw_data, 3, 4), 16, 10) INTO @days;
SELECT conv(substr(@raw_data, 7, 4), 16, 10) INTO @minutes;
SELECT "1900-01-01 00:00:00" INTO @start_date;
SELECT date_add(@start_date, interval @days DAY) INTO @date_plus_years;
SELECT date_add(@date_plus_years, interval @minutes MINUTE) INTO @final_date;

SELECT @final_date;

http://sqlfiddle.com/#!2/c960a/37

You can ofcourse inline everything or put it into a function, this is more of a proof of concept.

CAST(0x993902CE AS DATETIME) FROM SQL SERVER TO MYSQL

1 Answers1