35465

Convert DateTime to Hex equivalent in VB.NET

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

Answer1:

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.

Answer2:

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.

<strong>Edit</strong>

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.

Answer3:

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"

Recommend

  • How to translate this SQL Query to DQL?
  • How to convert hex to decimal in c#.net? [duplicate]
  • How to read date (Timestamp) from MongoDB using Java
  • how to retrieve URL from browser using c#
  • proc_open error when try to run artisan dump-autoload
  • Is canvas scaling more performant via CSS or JS?
  • gdb showing incorrect back trace by pointing at the wrong line of code
  • Which is faster: MySQL/PHP or serving straight from static files?
  • Generate a specific color for each string?
  • IOS Pushnotification in Arabic language
  • Object doesn't take value
  • Need help in optimizing a drawing code …
  • Ruby on Rails Active Record RANDOM() always the same within a loop
  • Fastest way to save/load data.table
  • Is there a way to group nth-child CSS rules for same parent
  • Rails + Amazon RDS : latency issues
  • Why integer division and modulo isn't optimized out in NVRTC
  • Making a vectorized numpy function behave like a ufunc
  • sending email using “bcc” without “to” in java application
  • DotNetOpenAuth - how to uniquely identify Google users?
  • How solve “Qt: Untested Windows version 10.0 detected!”
  • Thread safety of a fluent like class using clone() and non final fields
  • How to get address from latitude and longitude android google map v2 [duplicate]
  • Java Scanner input dilemma. Automatically inputs without allowing user to type
  • Illegal mix of collations for operation for date/time comparison
  • Master page gives error
  • Javascript Callbacks with Object constructor
  • How can I use Kendo UI with Razor?
  • Release, debug version and Authorization Google?
  • Arrays break string types in Julia
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • WPF Applying a trigger on binding failure
  • Hits per day in Google Big Query
  • Java static initializers and reflection
  • Can't mass-assign protected attributes when import data from csv file
  • sending mail using smtp is too slow
  • costura.fody for a dll that references another dll
  • Observable and ngFor in Angular 2
  • UserPrincipal.Current returns apppool on IIS
  • java string with new operator and a literal