Comparing SQL datetime to C# DateTime.Now

I’ve been trying to find a proper solution for the following problem. I have an SQL table with a time column (in this format: hh:mm:ss). It contains a starting time for a program.

I now want to compare the time from said column to DateTime.Now and whenever the time in the column is identical to the time now a method should be called (for example).

I have several problems now:

1. I normally use string startingtime = cmdx.ExecuteScalar().ToString(); to get a value from an SQL table but I’m not sure if it’s clever to convert the time to a string here

2. My program is a windows service and will always run and I’m not sure if it’s smart to use seconds when comparing the time from the SQL table to the time now because even a 1-second difference could mean the method won’t be called.

My first try was this:

string startingtime = cmdx.ExecuteScalar().ToString();         
string timenow = DateTime.Now.ToString("HH:mm:ss");

var u1 = TimeSpan.Parse(timenow);
var u2 = TimeSpan.Parse(startingtime);
Console.WriteLine(u1 - u2);

I could then use the difference between u1 and u2 and let’s say if it’s smaller than one minute the program should start. it’s an OK solution but there might be a better way, I’m looking forward to your solutions. Thanks in advance

Answer

Instead of converting the current date to a string and then using TimeSpan.Parse(), you should use the TimeOfDay property as it already returns a TimeSpan:

string startingtime = cmdx.ExecuteScalar().ToString();         

var u1 = DateTime.Now.TimeOfDay;
var u2 = TimeSpan.Parse(startingtime);
Console.WriteLine(u1 - u2);

Then, comparing the difference in time against a certain threshold should be the way to go. You may use something like this:

TimeSpan diff = u1 - u2;
if (diff.TotalSeconds > 60)
{
    // Call your method here.
}

Moreover, depending on the type of your column in the database, you might not need to call .ToString() and parse the result. If it’s a date type, something like this could work:

var u2 = ((DateTime)cmdx.ExecuteScalar()).TimeOfDay;

Otherwise, if it’s a string column, then TimeSpan.Parse(startingtime) would be fine.