1

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?

Mogsdad
  • 44,709
  • 21
  • 151
  • 275
  • is this a timestamp field? – Jahirul Islam Bhuiyan Aug 18 '15 at 17:32
  • what is the sample input Hex value, and expected output date value? – HaveNoDisplayName Aug 18 '15 at 17:33
  • Hm? I don't know. This are the values which are created when I script the datatables in SQLServer. They look like above and I need exactly such values. - sample input value is a normal date like: 1923-05-18 00:00:00.000. output like above –  Aug 18 '15 at 17:34
  • Take your date and subtract '1900-01-01' now the Days of the Timespan are the number of days to convert in hex, the time part is more complex but if you don't need it just add the missing 8 zero – Steve Aug 18 '15 at 17:38
  • Here the quasi-duplicate http://stackoverflow.com/questions/7412944/convert-datetime-to-hex-equivalent-in-vb-net – Steve Aug 18 '15 at 17:43

1 Answers1

1

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.

djangojazz
  • 14,131
  • 10
  • 56
  • 94