Convert DateTime to Hex equivalent in VB.NET

Question

How do I achieve the same in VB.NET which is so easily done in SQL Server.

SELECT CAST(GETDATE() AS VARBINARY(8)) --GIVES THE CURRENT TIME IN HEX

Now my question is how can I create the same string in VB.NET so that I can compare in SQL Server as such -

SELECT CASE WHEN GETDATE()=CAST(0X00009F5E00D8DF7C AS DATETIME) THEN 'TRUE' ELSE 'FALSE' END -- 0X00009F5E00D8DF7C WILL BE THE VALUE I GET IN VB.NET WHEN I CONVERT IT DATE.NOW() TO HEX

The SQL Server binary format for `datetime` is not a standard format, it consists of 2 integers. The first is the number of days since some point and the other is the number of `ticks` in the day where 300 ticks = 1 second. You can see this by `SELECT DATEADD(SECOND, CAST(0x00D8DF7C AS int)/300.0, DATEADD(DAY,CAST(0x00009F5E AS int),'19000101'))` Why would your application want to rely on / use this internal format? — Martin Smith, Sep 14 '11 at 08:04
Because our application is 3-tiered and because of different regional settings in different clients I would like to use internal format for date and time comparison. — Soham Dasgupta, Sep 14 '11 at 08:33
Sorry don't see how that's going to help. You still need to have a `datetime` to start with to convert to `binary` using SQL Server's method so you still need to ensure that this is in UTC time or whatever to get comparable results. — Martin Smith, Sep 14 '11 at 08:37
Yes it is possible but **there is no point** converting an inconsistent datetime from its native datatype to a binary representation won't magically fix the issue with different regional settings. For that to work you would need to ensure the clients were passing comparable datetimes pre conversion. i.e. use `DateTime.UtcNow()` not `DateTime.Now()` — Martin Smith, Sep 14 '11 at 09:00
What we do is create SQL query strings from our client side. While creating those we often use datetime from client forms and append to the query string and when we concatenate the datetime with the query string the dateformat of the datetime automatically change to the client format and when this query goes to execute on the application server with some other dateformat the query does not execute and raises an error `The conversion of a varchar data type to a datetime data type resulted in an out-of-range value`. This is the reason why if I want to convert to Hex then compare. — Soham Dasgupta, Sep 14 '11 at 13:01
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/3473/discussion-between-soham-dasgupta-and-martin-smith) — Soham Dasgupta, Sep 15 '11 at 04:30
I've just had a thought. Are you going this route to avoid issues with inconsistent representations of **the same** datetime - e.g. `dd/mm/yyyy` vs `mm/dd/yyyy`? If so you should use `yyyymmdd hh:mm:ss.nnn` as this is an unambiguous format to SQL Server and doesn't rely on internal details of the binary storage of a particular datatype. — Martin Smith, Sep 15 '11 at 07:01
A simple google search gives these results [http://www.eggheadcafe.com/software/aspnet/35371495/how-to-convert-utcdatetime-to-hexidecimal-and-back.aspx](http://www.eggheadcafe.com/software/aspnet/35371495/how-to-convert-utcdatetime-to-hexidecimal-and-back.aspx) [http://sim0n.wordpress.com/2009/03/28/vbnet-string-to-hex-conversion/](http://sim0n.wordpress.com/2009/03/28/vbnet-string-to-hex-conversion/) — Prasanth, Sep 15 '11 at 05:54
What output DateTime.ToBinary().ToString("x") gives me cannot be parsed as a valid DateTime hex in SQL Server. — Soham Dasgupta, Sep 15 '11 at 06:36
@Soham, that is because the `datetime` binary is not simple a count of ticks. — Jeff Ogata, Sep 15 '11 at 06:40

score 3 · Answer 1 · edited May 23 '17 at 11:54

This answer simply addresses conversion of .NET DateTimes to a binary format that is equivalent to SQL Server's datetime datatype, so I believe it is different enough that it warrants a separate answer (I checked here and here to be sure it was ok).

As @Martin Smith pointed out, the binary format of datetime is not simply a number of ticks since a specific point in time.

datetime is stored as 8 bytes, the first 4 bytes being the number of days since 01-01-1900 and the the second 4 bytes being the number of "ticks" since midnight of that day, where a tick is 10/3 milliseconds.

In order to convert a .NET DateTime to an equivalent binary representation, we need to determine the number of days since '01-01-1900', convert that to hex, and then the number of ticks since midnight, which is slightly complicated since a .NET tick is 100ns.

For example:

DateTime dt = DateTime.Now;
DateTime zero = new DateTime(1900, 1, 1);

TimeSpan ts = dt - zero;
TimeSpan ms = ts.Subtract(new TimeSpan(ts.Days, 0, 0, 0));

string hex = "0x" + ts.Days.ToString("X8") + ((int)(ms.TotalMilliseconds/3.33333333)).ToString("X8");

When I ran this code, dt was 9/14/2011 23:19:03.366, and it set hex to 0x00009F5E01804321, which converted to 2011-09-14 23:19:03.363 in SQL Server.

I believe you will always have a problem getting the exact date because of rounding, but if you can use a query where the datetime doesn't have to match exactly, down to the millisecond, this could be close enough.

Edit

In my comment under the first answer I posted, I asked about SQL Server 2008, because the datetime2 data type does store time with an accuracy of 100ns (at least, it does with the default precision), which matches up nicely with .NET. If you are interested in how that is stored at the binary level in SQL Server, see my answer to an older question.

Milliseconds is not necessary but accuracy of hour,minute and seconds are necessary and mandatory. I think that much precision will be preserved I guess? — Soham Dasgupta, Sep 15 '11 at 07:13
@Soham the largest difference I recall seeing was 3ms, so this should be ok. — Jeff Ogata, Sep 15 '11 at 15:52
Just out of curiosity, why do we have to divide the total milliseconds by `3.33333333` ? — Soham Dasgupta, Sep 16 '11 at 07:46

score 1 · Accepted Answer · answered Jul 25 '13 at 18:39

I had to convert some dates in dbscript from SQL Server's hex format string to standard datetime string (for use with TSQL to MySQL script translation). I used some codes I looked up in here and came up with:

        static string HexDateTimeToDateTimeString(string dateTimeHexString)
    {
        string datePartHexString = dateTimeHexString.Substring(0, 8);
        int datePartInt = Convert.ToInt32(datePartHexString, 16);
        DateTime dateTimeFinal = (new DateTime(1900, 1, 1)).AddDays(datePartInt);

        string timePartHexString = dateTimeHexString.Substring(8, 8);
        int timePartInt = Convert.ToInt32(timePartHexString, 16);
        double timePart = timePartInt * 10 / 3;
        dateTimeFinal = dateTimeFinal.AddMilliseconds(timePart);

        return dateTimeFinal.ToString();
    }

    static string HexDateToDateString(string dateHexString)
    {
        int days = byte.Parse(dateHexString.Substring(0, 2), NumberStyles.HexNumber)
                   | byte.Parse(dateHexString.Substring(2, 2), NumberStyles.HexNumber) << 8
                   | byte.Parse(dateHexString.Substring(4, 2), NumberStyles.HexNumber) << 16;
        DateTime dateFinal = new DateTime(1, 1, 1).AddDays(days);
        return dateFinal.Date.ToString(); 
    }

Maybe not optimized, but shows the idea.

score 1 · Answer 3 · answered Sep 15 '11 at 05:22

My first inclination is that the clients should not be constructing sql statements to be executed by your data access layer, but assuming you must get something working soon, you might consider using a parameterized query instead.

If you are making method calls from the client(s) to your other application tiers, you can construct a SqlCommand on the client and pass that to the next tier where it would be executed.

VB.NET is not the language I normally use, so please forgive any syntax errors.

On the client:

Dim dateValue As Date = DateTime.Now
Dim queryText As String = "SELECT CASE WHEN GETDATE() = @Date THEN 'True' ELSE 'False' END"
Dim command As SqlCommand = New SqlCommand(queryText)

command.Parameters.AddWithValue("@Date", dateValue)

If you must send a string, you could convert the DateTime to a string on the client and then convert back to a DateTime on the data access tier, using a common format.

On the client:

Dim queryText As String = "SELECT CASE WHEN GETDATE() = @Date THEN 'True' ELSE 'False' END"
Dim dateValue As Date = DateTime.Now
Dim dateString = DateTime.Now.ToString("M/d/yyyy H:mm:ss.fff", DateTimeFormatInfo.InvariantInfo)

Then send both queryText and dateString to the next tier in your application, where it would convert back to Date and again use a parameterized query:

Dim dateValue As Date

Date.TryParseExact(dateString, "M/d/yyyy H:mm:ss.fff", DateTimeFormatInfo.InvariantInfo, DateTimeStyles.None, dateValue)
Dim command As SqlCommand = New SqlCommand(queryText)

command.Parameters.AddWithValue("@Date", dateValue)

If your clients are in different time zones, you should (as @Martin Smith mentioned) consider using UTC time.

In .NET

Dim dateValue = DateTime.UtcNow

and also in your query, using GETUTCDATE():

Dim queryText As String = "SELECT CASE WHEN GETUTCDATE() = @Date THEN 'True' ELSE 'False' END"

But why can't I just convert the date to its varbinary equivalent in the client side and then convert it back to datetime in the query? Considering how badly our architecture is done, what you're saying means at least one more year of work just to refactor the codebase and use parameterized query. I mean that should also work right? And how will I be able to recreate that varbinary equivalent of datetime in VB.NET, I'm just very curious. — Soham Dasgupta, Sep 15 '11 at 05:29

Convert DateTime to Hex equivalent in VB.NET

3 Answers3

Linked