Search code examples
c#datetimemaxdate

Select max date and the date before

I have a data table which is updated every day except Saturday and Sunday. The problem lies in so that when I retrieve data with max (date) and max (date) -1

But it fails when I try to retrieve data for today (Monday) and yesterday (Sunday) when max (date) -1 does not exist.

the data can be updated on Saturday and Sunday, but since it's exchange rate I update. Will it give the same exchange rates Friday, Saturday and Sunday.

This is one way to solve the problem this way, but there is a better

string weekend = DateTime.Now.DayOfWeek.ToString();
if (weekend == "Monday")
{
select ***** where max(date)-3 from *****
}
Solution

  • Here's some other options which work with an arbitrary target_date as well.

    If you only have one record for each date,

    SELECT * FROM table WHERE date<=target_date ORDER BY date DESC LIMIT 2

    If you have many records per date,

    SELECT * FROM table WHERE date IN (SELECT DISTINCT date FROM table WHERE date<=target_date ORDER BY date DESC LIMIT 2)

    You could also use the date/time functions of your database to check for Monday, of course.