Hey, after recently answering this thread, I found the original one here so I thought I'd share how I solved it.

I've come across this before, but basically what you have there is a date stored in binary.

DECLARE @number AS BIGINT

SET @number = 131401986

I'll be using the above variable throughout this post, the one posted in the other thread. First, we need to cast the number as binary.

SELECT CAST(@number AS BINARY(8))

/*OUPUT

----------

0x0000000007D50902

*/

This binary number is split into three parts, the first two bytes represent the year.

SELECT CAST(0x0000000007D5 AS INT)

/*OUPUT

-----------

2005

*/

So, we can grab the year part with a cast of a substring of the number.

SELECT CAST(Substring(( CAST(@number AS BINARY(4)) ), 1, 2) AS INT)

From here it just becomes a case of knowing how the rest is laid out. I believe the next 1 byte is the month and the final 1 byte is the day.

SELECT CAST(Substring(( CAST(@number AS BINARY(4)) ), 1, 2) AS INT)

SELECT CAST(Substring(( CAST(@number AS BINARY(4)) ), 3, 1) AS INT)

SELECT CAST(Substring(( CAST(@number AS BINARY(4)) ), 4, 1) AS INT)

/*OUTPUT

-----------

2005

-----------

9

-----------

2

*/

So finally, we can use the following query to get convert it to datetime.

SELECT CAST(CAST(CAST(Substring(( CAST(@number AS BINARY(4)) ), 4, 1) AS INT) AS

VARCHAR) +

'/' + CAST(CAST(Substring(( CAST(@number AS BINARY(4)) )

, 3, 1)

AS

INT) AS VARCHAR) + '/' + CAST(

CAST(Substring((

CAST(

@number AS

BINARY

(4)) ), 1, 2

) AS

INT) AS VARCHAR) AS DATETIME)

This changes considerably when "time" is involved as well.