MySQL Forums
Forum List  »  Newbie

Re: Can datediff be used?
Posted by: Anthony Willard
Date: December 08, 2006 03:24PM

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

Options: ReplyQuote


Subject
Written By
Posted
December 08, 2006 03:18PM
Re: Can datediff be used?
December 08, 2006 03:24PM
December 09, 2006 06:42AM
December 11, 2006 11:53AM
December 11, 2006 01:16PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.