SQL Server 2019 date datatype comparison

Question

I have two columns in a table that are defined as date columns, populated with dates.

If I query the table comparing the two date columns using the <> operator, columns with the same date do not always show as equivalent. e.g.

select datediff(day, date1, date2), date1, date2 
from myTable 
where date1 <> date2

Returns some columns where where date1 = date2, and shows 0 for datediff.

If, however, I convert both date columns to date in the where clause, the query returns the expected results, e.g.

select datediff(day, date1, date2), date1, date2 
from myTable 
where convert(date, date1, 101) <>  convert(date, date2,101)

The query correctly returns ONLY the rows where date1 <> date2.

Will the date datatype hold values like "12/1/2023" and "2023-12-01" and treat them differently?

[UPDATE]

Thanks for all the answers. the fields are definitely "date"...here's the table structure (using SSMS "script table as create to...")

CREATE TABLE [dbo].[rates](
[sIncStart] [date] NULL,
[sIncEnd] [date] NULL,
[sPaidStart] [date] NULL,
[sPaidEnd] [date] NULL,
[aIncStart] [date] NULL,
[aIncEnd] [date] NULL,
[aPaidStart] [date] NULL,
[aPaidEnd] [date] NULL)

and here's the query output:

select top 5 datediff(day,sIncEnd,aIncEnd) diff, sIncEnd, aIncEnd
from rates 
where sIncEnd <>  aIncEnd

diff	sIncEnd	aIncEnd
0	2018-06-30	2018-06-30
0	2016-12-31	2016-12-31
0	2018-01-31	2018-01-31
0	2019-01-31	2019-01-31
0	2017-12-31	2017-12-31

or

select top 5 datediff(day,sIncEnd,aIncEnd)diff, sIncEnd, aIncEnd
from rates
where convert(date,sIncEnd,101) <> convert(date,aIncEnd,101)

diff	sIncEnd	aIncEnd
-365	2021-08-31	2020-08-31
366	2024-01-31	2025-01-31
366	2023-12-31	2024-12-31

Note that there are only 3 rows in the table that truly have different dates in the two columns.

I only mentioned format differences because this table may be populated from more than one source, but these are the actual queries and data returned from the table.

I'll try to reproduce from scratch, but if I can't reproduce it, I'm not sure what to do short of creating a new table and trying to copy all the data into it and seeing if that cleans up the data or table structure. This is production data.

Answer 1

Will the "date" datatype hold values like "12/1/2023" and "2023-12-01" and treat them differently?

No.

If you really defined a column with Date as the date type, the value stored is binary and not human-readable at all, regardless of how you input the data. Anywhere you see human-readable dates is a convenience provided by your tooling.

But... depending on the cultural settings on your database server, you could INSERT data in an SQL statement with literals like 12/1/2023 or 2023-12-01, and it's possible the first would be interpreted and stored as January 12, while the latter would be interpreted and stored as December 1.

In the realm of SQL Server, specifically, for historical reasons there are some edge cases when dealing with date-only values (no time component), such that neither of those is correct for including dates in an SQL string. Instead, you should use the unseparated version of the ISO-8601 standard format: yyyyMMdd. Thus, the SQL literal for that date should look like this: 20231201. Really. Any other format in an SQL string for SQL Server is wrong.

Furthermore, when working with SQL commands within a program you should use parameterized queries, where you set a parameter value instead of concatenating the data into an SQL command string. And if that platform is strongly-typed, this should use the platform's Date or DateTime type, whatever that is; it should not convert the value to a string at all. Let the SQL Server connection library handle this instead, and just give it your Date/DateTime object.

In general, if you find yourself converting a strongly-typed Date value into a specific format for use in an SQL statement, you're doing something very wrong, that may even point to a security vulnerability.

---

But there's more.

Returns some columns where where date1 = date2, and shows 0 for datediff.

Remember, my second word was, "If".

Clearly, a stored January 12 value is more than 0 days away from a stored December 1 value. In this case, the only reasonable conclusion is a column is not actually a Date type. At least one of the columns is either really a DateTime or similar, with a non-0 time component, or (God forbid) a varchar which happens to store strings that merely look like dates.

Specifically, the fact converting both sides of the conditional expression to Date types gives different results proves you don't have the data type you think you do for at least one of the columns. Unless you can give us an example of this in db fiddle, this is the only reasonable conclusion.