CODEWITHSUNDEEP
c#sqldatetimecastinghex
user4671342
user4671342

Reputation:

How to convert from datetime to store as Hex in SQL?

I have existing datatables that include a field of hexadecimal data which is actually a date.

From existing code, I know that data is converted to a DATETIME values with:

SELECT CAST(0x0000214900000000 AS DATETIME)

My problem now is that I need to insert values into such fields manually, but I don't know how to generate the hexadecimal value for a given DATETIME.

I have tried to insert AS BINARY(8) but that does not return the correct value like above.

How do I perform this conversion?

Upvotes: 1

Views: 5159

Answers (1)

djangojazz
djangojazz

Reputation: 13252

If you are doing this ALL in SQL here is a simple example that exists all in memory. You can run this as is in SQL Management Studio and the syntax should be fine for 2008 SQL Server and up.

DECLARE 
    @Date   DateTime = getdate()
,   @Hex    varbinary(8)
;

DECLARE @Temp TABLE ( value varbinary(8) );

INSERT INTO @Temp VALUES (0x0000214900000000),(cast(@Date AS varbinary));

Select
    value
,   cast(value AS DateTime)
from @Temp

SELECT @Hex = cast(cast('2015-04-01' AS DateTime) AS varbinary)

INSERT INTO @Temp VALUES (@Hex)

Select
    value
,   cast(value AS DateTime)
from @Temp

You basically 'cast' an object type to a different object. Yet for varbinary if you are storing this you need to specify a length as well. You can cast back and forth in SQL quite easily if the type conversion is allowed. If you wanted to do this for insertion from .NET code directly that may be slightly different.

Upvotes: 2

Related Questions