Getting the closest weekday to today in from a matrix (weekdays in ddd format)

I've been given a bit of a head ache when I was asked to see if i could get a simple solution to a problem at work.

This is the current situation:

We have a date (Mostly todays date) from which I can easily get the Weekday parameter using dEarliest.ToString("ddd");
In a database we have a register of which day a certain task is to be performed with the weekdays stored as "ddd" format (i.e Mon, Tue, Wed, Thu etc).

And, the problem, How can I get which day in the database records that are the closest to today? I've been sitting here trying to figure it out with some "smart" SQL query, but I just don't know how to wrap my head around it.

I was thinking enum:s, but I'm not sure if that'd work, and if it would, how? I'm quite new at c# hence the, probably, stupid question.

Any help would be greatly appreciated. :)

Solution

The best way to do this is to make a comparrison between the DateTime's DayOfWeek value (the property DayOfWeek is of type enum DayOfWeek) and the output of SQL Server's DATEPART function.

For example, if you wanted to get all the tasks for yesterday, you might do this in C#:

int yesterday = ((int)(DateTime.Now.DayOfWeek))-1;

and then pass that value into a SQL statement that does this:

SELECT * FROM <table> WHERE DATEPART(weekday, <column_name>) = (@yesterday+1)

The @yesterday+1 part is because C#'s enum uses 0-6 for the days while SQL Server uses 1-7 (both uses Sunday as the first index).