• Good question, which illustrates a point that has been known to trap inexperienced people and may help some of them for falling into the trap of thinking that when comparing a date and a datetime the date is implicitly converted to a date rather than the date being implicitly converted to a datetime.

    But the description

    I am extending Dwayne Dibley's question to show how 'Time' part in date is considered by SQL server

    at the front of the question is a little inaccurate, albeit rather amusing. There is no 'Time' part in date, the issue being illustrated is actually what time part will be used in the result of an implicit conversion of a date (which has no time part) to datetime (which has to have one) [or for that matter to datetime2, or smalldatetime - all three datetime types have higher type precedence that date].

    Tom