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

Getting Started
1

Hello,
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"
case today.today.sqldate
    msgbox "Today"
case tomorrow.sqldate
    msgbox "Tomorrow"
end select

Thanks all,

2

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

3

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"
  
Else
  
  Return "Tomorrow or later"
  
End If

But remember that the hours and minutes are ignored here!

4

But remember that the hours and minutes are ignored here!

Important.
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"
else
if datSelectedDateTime.SQLDate > (today.SubtractInterval(0,0,1)).SQLDate then
return "Tomorrow or later"
else
return "Yesterday or sooner"
end if

end if
1 Like
5

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

6

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”.

2 Likes
7

Nice! Thank you. :+1:

1 Like
8

The ANSI SQL date format was designed for simple comparison.

4 Likes