Re: Can datediff be used?

[Anthony Willard]

I would create a reference table with all applicable dates, then perform an outer join against it. Anything that is not in your referenced table will show up.

Like:

SELECT refDate
FROM myRefDate r
LEFT OUTER JOIN myOtherTable o ON o.date = r.refDate
WHERE o.date IS NULL

You can either build the table ahead of time, or generate it dynamically for the range in question. In reality each year will only have 365 rows, so 10 years of date fields would only have 3,650 rows, with an index, it would only take up about 60 kb. Not really a waste.

Also, rename your column names to not be reserved words or data types. It makes it confusing.

Anthony