date vs datetime vs datetime2 in SQL Server

A birthday is a date. An order stamped at 09:14 is a datetime (or datetime2). Mixing them is how “yesterday” leaks today.

Pick one

SELECT CAST(GETDATE() AS date) AS just_day,
       GETDATE() AS datetime_now,
       SYSDATETIME() AS datetime2_now;

• date — no time. Filters are simple.
• datetime — old default, odd rounding, range starts 1753.
• datetime2 — prefer this for new timestamp columns.

How to filter a timestamp by a day is on datetime to date.