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
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.
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.
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"
