The current time (in C#), as I type, is 12/07/2010 19:21:36. That's achieved by using Console.WriteLine(DateTime.Now); on a box with UK regional settings. According to SQL Server, it's now 2010-07-12 19:21:36.957. That's obtained through executing the following query: SELECT GetDate(). So we know that DateTime.Now and GetDate() are the ways to get the current date and time in C# and SQL respectively.
In C#, there is an AddDays() method that takes an integer. That integer can be negative, so the following will obtain the date and time for yesterday: Console.WriteLine(DateTime.Now.AddDays(-1));. SQL has a similar function: DateAdd(). This takes an interval or datepart as they are known, an integer (which can also be negative) and a datetime. There are a number of accepted values for the datepart argument, which can be found here. To get the same result as the preceding C# code, you would simply use SELECT DATEADD(d, -1, GetDate()).
So far so good, if all you need is the date and time for 24 hours ago (11/07/2010 19:21:36). However, and here's the rub - often, you might want to obtain all events that happened yesterday (or the day before today) from a collection or a database table. If you were to use the preceding SQL example in a statement like this:
SELECT * FROM Table1 WHERE EventTime < DATEADD(d, -1, GetDate())
you will get all items that have an EventTime value before yesterday at 19:21:36. All items that occured after that time will not be included. Not quite what you would expect, maybe. The same problem exists if you are querying a collection of C# objects using DateTime.Now.AddDays(-1) as the basis for the comparison. Try this example code:
var times = new List<DateTime>(); for (var i = 1; i <= 48; i++) { times.Add(DateTime.Now.AddHours(-i)); } foreach (var t in times.Where(t => t < DateTime.Now.AddDays(-1))) { Console.WriteLine(t); } Console.ReadLine();
All it does is build a List<DateTime> with items 1 hour apart, going backwards from now. However, it will only select those items that have a value prior to 19:21:36 yesterday, which leave some events from yesterday still untroubled - ie those between 19:21:36 and midnight.
It's the pesky time part that gets in the way, so that needs to be changed to 00:00:00 or midnight. Previously, this article showed how to do that with C#, until Steve contributed his comment below, pointing out that DateTime.Now.Date gives us exactly that. However, in SQL, the solution is to change the datetime to a string:
[C#] var yesterday = DateTime.Now.Date; foreach (var t in times.Where(t => t < yesterday)) { Console.WriteLine(t); }
[SQL] SELECT * FROM Table1 WHERE EventTime < CONVERT(varchar(10), GETDATE(), 101)
As someone once said: Hope This Helps.
