How to replace a date with the strings "today", yesterday?

How to replace a date with the strings “today”, “tomorrow”, “after tomorrow/Late”?
I tried this without success:

Var today As DateTime = DateTime.Now
Var yesterday As DateTime = today - 1
Var Late As DateTime = today - 2

Select case today.sqldate
  case Is <=yesterday.sqldate
    msgbox "Late"
    msgbox "Today"
case tomorrow.sqldate
    msgbox "Tomorrow"
end select

Thanks all,

The way you have those assignments, today will never be less than yesterday.

Should be something more like:

If datToday.SQLDate = datSelectedDateTime.SQLDate Then
  Return "Today"
ElseIf datSelectedDateTime <= datToday.SubtractInterval(0,0,1) Then
  Return "Yesterday or sooner"
  Return "Tomorrow or later"
End If

But remember that the hours and minutes are ignored here!

But remember that the hours and minutes are ignored here!

If it is 11am, then (now- 1 day) is yesterday at 11am
But ‘yesterday’ ended at midnight, so ‘yesterday’ is a variable amount of time away compared to ‘now’ if the time is taken into account.

This is pretty much solved if you turn date or datetime into a basic SQLDate

from the docs:

SQLDate As String
Returns the date in SQL date format, YYYY-MM-DD.

so as an example, if we start Tomorrow (chosen because its the end of the month!)

Now.SQLDate would be 2023-02-28
Yesterday.SQLDate would be 2023-02-27
Tomorrow.SQLDate would be 2023-03-01

And because they are strings, the comparison is easy

If today.SQLDate = datSelectedDateTime.SQLDate then  
return "Today"
if datSelectedDateTime.SQLDate > (today.SubtractInterval(0,0,1)).SQLDate then
return "Tomorrow or later"
return "Yesterday or sooner"
end if

end if
1 Like

Did not know that comparing SQLDate is possible. Clever, thank you. :slight_smile:

It’s a string that is in a fixed format (number of digits for the day, month year etc) no matter the values and in ordered in largest component to smallest (year then month then day etc). So simple string comparison works as you would expect dates to compare.

It’s a trick that works with any item, so long string format is constant. For example you can compare two numbers using string comparison so long as each has the same number of decimal places and same number of leading digits. It will sort as a real number would. Sorting strings “1”, “10” and “2” will not sort correctly. However, " 1", “10” and “20” will, as would “01”, “10” and “20”.


Nice! Thank you. :+1:

1 Like

The ANSI SQL date format was designed for simple comparison.