The DateTime data type ALWAYS stores the date AND time. So you are left with using CONVERT/CAST to obtain a particular format, or use the YEAR(), MONTH() or DAY() methods to isolate date details depending on your need.

SQL Server Date Formats .

The easiest solution is just to not expose the time portion to the user. However, if you really need to make sure only the date part is stored, you could force the time portion to midnight/midday/any constant time before storing the value.

The built-in DATETIME data type stores both the date and time data. If you specify only the date portion then the time will be 12:00:00 or something like that.

Funny story: I saw a database once where there was a date and a time field, both stored the date and the time, but each was used only for half of the data. Some people do silly things :)

If you cast a DateTime to an Int and back you will get a DateTime with 00:00 as the time part. So you could save all your dates as integers in the database.

Either add a computed column:

dateonly AS CONVERT(DATETIME, CONVERT(CHAR(8), date_with_time, 112), 112)

or truncate your date right on insert:

INSERT
INTO     mytable (dateonly)
VALUES   CONVERT(DATETIME, CONVERT(CHAR(8), GETDATE(), 112), 112)

, making a CHECK on your dateonly column to raise an error when someone tries to insert a non-truncated value:

CHECK (dateonly = CONVERT(DATETIME, CONVERT(CHAR(8), date_with_time, 112), 112))

Just represent the date as a yyyMMdd integer value.

I had the same issue as the original post, and very stupidly setup my date tables to be varchars() instead of datetime, and then stored the dates using a foward slash. Of course, now the date comparison operators will not work. However, of interest is that I found that if I simply replace the forward-slashses with dashes, then the comparison operators do work properly. So for now, for me at least, storing JUST the date is possible, by using a varchar and storing the date as a string, using dashes (e.g., 12-01-2023). I don't know why this is working, but it is.