Finding Yesterday in SQL and C#

Here's something that comes up often in forums - How To Find Yesterday in SQL or C#. Piece of cake, if you know how, but tricky if you don't. And especially tricky to get the right value if you are not clear on the requirement.

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.

 

Date Posted:
Last Updated:
Posted by:
Total Views to date: 16758

4 Comments

- Mike

Thanks man!, actually used that snippet concept today lol

- Steve

what about:

var yesterday = DateTime.Now.Date;

- Mike

@Steve,

Yes - that will do it :o)

- dotnetcoder

Simple and very informative!

Recent Comments

Vijay 5/25/2015 2:12 PM
In response to How To Send Email In ASP.NET MVC
Thanks...

RR 5/25/2015 8:58 AM
In response to Web Pages - Efficient Paging Without The WebGrid
@MIke.. is it possible to combine pagination and filter (change category ) in the script? Thanks...

Irfan Khan 5/25/2015 6:19 AM
In response to Highlighting Keywords Found In Search Results
This is truely helpful. Thank you for the brilliant tip....

Manoj Kulkarni 5/25/2015 4:46 AM
In response to Custom TagHelpers in ASP.NET MVC 6
Thank you for nice article. Really helpful....

Sean 5/21/2015 4:20 AM
In response to ASP.NET MVC 5 with EF 6 - Working With Files
Thank you for the tutorial. Saving to the database works, but the save FilePath is not working. It...

KrASh 5/19/2015 11:10 PM
In response to Adding Search
Hello Mike, I have a question about the second search. In one line it has a where with the title and...

KrASh 5/19/2015 11:04 PM
In response to Accessing Your Model's Data from a Controller
Thanks, I had some silly mistake, can't remember now what it was (had some personal problems last i...

Satyabrata Mohapatra 5/18/2015 4:29 PM
In response to Error Handling in ASP.NET Razor Web Pages
Great article. Learned a lot....

Corstian Boerman 5/13/2015 12:59 PM
In response to ASP.NET 5 Middleware, Or Where Has My HttpModule Gone?
It looks as if this technique doesn't work anymore. (As of the VS 2015 RC release) When adding it...

Alvin 5/12/2015 1:20 PM
In response to Razor Web Pages E-Commerce - Adding A Shopping Cart To The Bakery Template Site
Maybe ability to update quantity of the items once you are in the "Review Cart" step. Also, if on...