SQL DATEPART(dd, getdate()) vs VB.NET Today.DayOfWeek
WARNING: THIS POST IS REALLY NERDY.
While developing a calendar application, I ran into an issue that took hours to diagnose and nearly prompted me to wrap my car around a tree. The issue is this…
I was taking a date from SQL, using the DATEPART function to return an integer value for the day of the week, then comparing that to the result of the DayOfWeek function in VB.NET. Here’s how it should work:
SQL
DECLARE @KillMe AS datetime;
SET @KillMe = ‘5/4/2011’;
SELECT DATEPART(d, @KillMe);
VB.NET
Dim KillMe As Date = CDate(‘5/4/2011’);
Return KillMe.DayOfWeek
So assuming that SQL considers Sunday = 1, it will return an integer value of 4, and since I’m using the same date, VB.NET should return a 4 as well… right?
Wrong.
Apparently, GETDATE() in SQL returns 1 for Sunday and goes from there, while VB.NET starts at 0 for Sunday and ends at 6 for Saturday. Don’t ask my why this took so long to figure out, but I suspect that four failed attempts at a passing grade in Intermediate Algebra might be only partially at fault, while Microsoft holds the lions share of the blame. All that having been said, here’s the fix:
VB.NET
Dim KillMe As Date = CDate(‘5/4/2011’);
Return CInt(KillMe.DayOfWeek) + 1
It’s hinky and lame, I know, but I decided that correcting SQL by subtracting 1 was more complicated.
