If a date is in the format 2020-01-01 00:00:00.000, when we do [Date] <= '2020-01-01', it will not return 2020-01-01 00:00:01.000.
Is that corrent?
Shouldn't the 2020-01-01 00:00:01.000 be considered as '2020-01-01' ?
At the same time, if I do CONVERT(date,[Date]) <= '2020-01-01', this will return the 2020-01-01 00:00:01.000.
Is there a way to specify a global conversion of dates with a single line statement without having to repeat numerous CONVERT(date,[Date]) statements? E.g. IIF Table.* IS DATE THEN CONVERT(date,*)
Simple
2020-01-01 00:00:01.000 is not less than
2020-01-01. Look at it. First one is one second ahead. So its doing what you asked it.
if you want a global change here are your options
create a view that does the conversion and use that view everywhere else, this has been recommended to you on many other occasions in your many other questions. You only need to implement this recommendation made by highly skilled SQL gurus on this site
change the data so all date fields do not include the time stamp if not needed. this is drastic and from other threads from you it seems like you do not have permission to do this.
another option is to dump the data into #temp table with the date column converted to the way you want it.
When you use '2020-01-01' - SQL Server implicitly converts that to the appropriate data type, depending on data type precedence. If the column on the left side of the operator is a datetime column - the string will be converted to '2020-01-01 00:00:00.000'.
So you are now comparing '2020-01-01 00:00:01.000' <= '2020-01-01 00:00:00.000'
And this is not true so it isn't returned.
Comparing dates where there is a time component should always be done using an open half-interval range. For example:
WHERE datecolumn >= '2020-01-01' -- converts to '2020-01-01 00:00:00.000', inclusive
AND datecolumn < dateadd(day, 1, '2020-01-01') -- returns '2020-01-02 00:00:00.000', not inclusive
This will return everything from the beginning of 2020-01-01 through the end of the day.