Compare day and show hour only using DateTime formats

I have a DateTime column in my SQL database, how do I check if the Day in that is Today, or yesterday?

What I want to is something like: If date is today then the result would be "Today, at " 开发者_JS百科same for yesterday..

DateTime.Now.Subtract(dt).Days == 0 ? "Today" : "Yesterday"

It's inline. dt is your variable, type - DateTime

On the .NET side you can check for the date being today:

if (testDate.Date == DateTime.Today)
{
    ...
}

DateTime.Today

An object that is set to today's date, with the time component set to 00:00:00.

You can then use:

if (testDate.Date == DateTime.Today.AddDays(-1))
{
    ...
}

to test for the date being yesterday.

DateTime.AddDays

The DateTime.Equality operator is defined.

You could do this is SQL

Create Table #t
(
DateCol datetime
)

insert Into #t
values (getdate())

insert Into #t
values (getdate()-1)

insert Into #t
values (getdate()-2)

Select Case When Cast(Floor(Cast(DateCol as float)) as DateTime) = Cast(Floor(Cast(GetDate()as float)) as DateTime) Then
        'Today, at ' + convert(varchar(2), DatePart(hour, DateCol)) + ':' + convert(varchar(2), DatePart(minute, DateCol))
        When Cast(Floor(Cast(DateCol as float)) as DateTime) = Cast(Floor(Cast(GetDate()-1 as float)) as DateTime) Then
        'Yesterday, at ' + convert(varchar(2), DatePart(hour, DateCol)) + ':' + convert(varchar(2), DatePart(minute, DateCol))
        else 'More than one day old' End

From #t

drop table #t

Ah I now see that you want done in .NET via LINQ.

I'll leave this here anyway - someone may find it useful

In Sql you can do something like this:
SELECT CASE
WHEN DateDiff(dd, Created_Date, getdate()) = 0
THEN
'Today at: ' + CONVERT(VARCHAR(20),Created_Date )
WHEN DateDiff(dd, Created_Date, getdate()) = 1
THEN
'Yesterday at: ' + CONVERT(VARCHAR(20),Created_Date )
END
FROM tableName

MySQL

Today:

DATE(DateColumn) = CURDATE()

YesterDay:

DATE(DateColumn) = CURDATE() - INTERVAL 1 DAY

C#

var otherDate = new DateTime(2010, 06, 09);
var span = DateTime.Now - otherDate;
Console.WriteLine(span.Days); // 1

There are lots of special cases, so I think this should be encapsulated in a method, like this:

public static string GetDateDescription(DateTime date)
{
  Calendar calendar = CultureInfo.CurrentCulture.Calendar;

  if (date.Date == DateTime.Today)
    return "Today";

  if (date.Date == DateTime.Today.AddDays(1))
    return "Tommorow";

  if (date.Date == DateTime.Today.AddDays(-1))
    return "Yesterday";

  if (date.Date == DateTime.Today.AddDays(2))
    return "The day after tommorow (dress warm)";

  int dateWeek = calendar.GetWeekOfYear(date, CalendarWeekRule.FirstDay, DayOfWeek.Monday);
  int todayWeek = calendar.GetWeekOfYear(DateTime.Today, CalendarWeekRule.FirstDay, DayOfWeek.Monday);

  if (dateWeek == todayWeek)
    return "This week ...";

  if (dateWeek - todayWeek == 1)
    return "Next week ...";

  if (dateWeek - todayWeek == -1)
    return "Previous week ...";

  if (date.Month == DateTime.Today.Month)
    return "This month ...";

  if (date.Month - DateTime.Today.Month == 1)
    return "Next month ...";

  if (date.Month - DateTime.Today.Month == -1)
    return "Last month ...";

  if (date.Month - DateTime.Today.Month > 1)
    return "Far, far away...";

  if (date.Month - DateTime.Today.Month < -1)
    return "Long time ago...";

  return date.ToShortDateString();
}

and used like:

DateTime start = DateTime.Now.Date.AddDays(-60);
DateTime end = DateTime.Now.Date.AddDays(+60);

for (DateTime current = start; current < end; current = current.AddDays(1))
{
  Console.WriteLine("{0} --> {1}",
     current.ToShortDateString(), GetDateDescription(current));
}

This works with dates, so if your date is in a datarow field, you'll need to cast it to a DateTime value, either

DateTime date=(DateTime) p.CreatedDate;

or

DateTime date = Convert.ToDateTime(p.CreatedDate);