I am building an app that has a daily quote that should be stored in the database. Each quote is assigned with a day of the year, including one for Feb 29th. Since the quote only cares about the day not the year should I still use smalldatetime type? Please let me know your opinions, thanks!!
I had this problem recently, my initial design did store the date and I just ignored the year. However, it just didn't feel right. I decided to just remove it and have a separate
Month column instead. It just felt a lot cleaner and much more readable.
Long time since I wrote this answer, however, in hindsight I hold my hands up and say the comments were naively overlooked. By storing the day/month as separate fields there is the potential for storing invalid data whereas if you stored them as a full
DateTime you are effectively getting that validation for free.
Depending on your validation policies this may not be a concern, however, if you rely on DB validation then I would advise you either store it as
DATE and simply pull out the relevant information or use a trigger to run some validation before insert.