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 开发者_StackOverflow中文版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.

Update

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.

---

Another option (I don't think anyone else has offered) would be to store the month and day as separate ints. So, to find todays entry, you could:

select quote from quoteTable where month = 4 and day = 20;

This would allow you to have day specific messages without using dates (and ignoring the year).

Just an idea.

---

It depends on what you have to do with those dates. If having the year in your db is not a problem then you can take a leap year and use that for storing dates, ignoring it in your app view.

---

If you need to retain the day and month data, you might as well use SmallDateTime and simply ignore the year component (or set it to the same value across the board, for example 2000 which was a leap year, so leap dates will be allowed).

You still get to use proper date and time functions with the correct data type and if you go with a VARCHAR field you will end up converting to and from it.

---

I can't help feeling that if the calendar had been invented by a software engineer that the leap day would be the 32nd of December rather than the 29th of Feb. That way you could simply use a smallint offset from the 1st Jan.

You can still use a smallint offset from the 1st of March, with 1st March as 0, 2nd March as 1, 29th Feb as 365. However it involves doing some kind of custom conversion to the figure you want and may not sort as you would like to.

Given that you can store the Day and Month as two tinyints taking up the same space, I'm not sure this would be a good plan, but thought I would mention it for completeness.

---

Since there is no Interval type like Oracle, then you have one of a couple of choices that come to mind.

• Store the year when using datetime / smalldatetime, it is going to cost you nothing extra to store it, just choose not to display it.
• Adopt a DW type approach with a date table and link to it using PK/ FK
• Use a non date based type such as smallint or varchar, although this may well result in some difficulties in getting queries to remain sargable and avoid scans.

---

How about a straight running number. You could choose the quotes at random each time and mark another boolean field as they are chosen. You can reset the boolean field at the end of the year.

This also allows you to add more quotes to the database as time goes without having to delete the ones you already have.

---

You could still use a datetime column in your database and use the DatePart() SQL function to retrieve the day of the year.

SELECT datepart(dy,myDateColumn) FROM myTable

---

I would avoid using a datetime for this. In a sense, you'd be storing incorrect data. For example, you'd be storing 4/20/2010 as 4/20/2012 or whatever year you chose. Even though the year doesn't matter to your application, this approach could lead to some unexpected problems.

For example, what if you somehow got a date in there with the wrong year? Your calculations would be wrong.

Since there's no native type to support what you're doing, I would store the values as varchar and do any necessary calculations in a user-defined function.

---

How about a varchar with 0305 being March 5.

---

You could also consider using a single int to store a day of the year.

It could be a little bit painful to translate between human-readble format and day-of-the-year. On the other hand, it will be very easy to assign the dates to the quotes and to select them.

SELECT quote FROM QuoteTable 
WHERE dayOfYear = DATEPART(dy, GETDATE())

---

CONVERT(VARCHAR(5),GETDATE(),101)