I’m facing a problem when trying to find the difference between dates using my SQL Server query. The results I’m getting seem incorrect.
Here’s the query I’m using:
SELECT BASESCORE,
MAX(CONVERT(DATETIME, '')) - MIN(CONVERT(DATETIME, '')) AS date_difference
FROM log
GROUP BY BASESCORE
ORDER BY BASESCORE
It runs properly, but the output is consistently showing:
1900-01-01 00:00:00.000
1900-01-01 00:00:00.000
1900-01-01 00:00:00.000
This result isn’t what I expected. How can I adjust my query to get the correct date difference?
The issue stems from converting empty strings to DATETIME, which SQL Server interprets as the default date of 1900-01-01. When you subtract identical dates, the result is zero, displayed as the base datetime value. You need to replace the empty strings with actual date column names from your log table. For example, if your date columns are named start_date and end_date, your query should read: MAX(CONVERT(DATETIME, start_date)) - MIN(CONVERT(DATETIME, end_date)). Additionally, consider using DATEDIFF function for cleaner syntax and specify the unit of measurement you want (days, hours, etc.). This will give you meaningful numeric results rather than datetime objects.
hmm, are you maybe missing the actual column names inside those quotes? what columns in your log table contain the date values you want to compare? also curious - what kind of date difference are you trying to calculate exactly?
looks like your converting empty strings to datetime which defaults to 1900-01-01. you need actual date columns or values in those CONVERT functions instead of just ‘’. what are the actual column names for your dates?