Much has been written about the difference between SQL Server, Unix, and Microsoft OLE Automation Time epochs. SQL Server treats 1/1/1900 as the epoch, while OLE treated it as 12/30/1899 for various reasons. I've read all the history, and it's fascinating, but doesn't help me solve my problem.
I find myself writing a lot of queries on fields that store only the time. I know SQL 2008 has a TIME datatype, but I am not using SQL 2008, and even if I was, I don't think .NET yet supports it.
So, using Linq to SQL, I have code that either does something like this to deal with the fact that applications write the date into the database as both date and time using the ole automation epoch date (1899) rather than the SQL epoch date (1900):
where date.TimeOfDay > DateTime.Now.TimeOfDay
The problem is that this generates nasty SQL with lots of DATEADD calls to extract only the time part of the date for comparison. It's cleaner on the SQL side if I do the date manipulation before i do the comparison. Something like:
where date > new DateTime(1899, 12, 30) + DateTime.Now.TimeOfDay
That's a lot of code to litter around the place, and while yes I could (and have) made methods to factor it out, I hate having to write this code every time i work on a new project.
I would rather just be able to do
where date > DateTime.Now.ToEpoch()
I actually implemented this as an extension method, and it works. However, I have to think this is a common enough problem that there are better solutions. I recall Jon Skeet was working on some time library, I don't know if it handles this situation or not..
Any other suggestions?