Mike Mike - 23 days ago 5x
SQL Question

What is the best way to store a date without a year in SQL database?

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 Day/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.