Using SQL Server 2008 Date Data Type to Store Year

Does it make any sense to store just a year in开发者_JAVA百科 the new Date SQL type in SQL 2008? My boss wanted this but we're prompting the customer to enter year optional in a web form..just 4 integers. I don't see why we'd use Date datatype as that also includes day and month and I've already got those seperated out into int fields in our DB table.

Year is optional on our web form, so if they only enter Month and Day, it makese no sense to me to store this in a date type of field because year will have to be something abritrary. I guess we could do 01-16-1900 if they didn't enter year and then we'd have just day and month..but whatever, to me that's a hack. I'd rather seperate all 3 into seperate fields only because we have one of those values (year) as optional. I don't see that we'll ever require year because it's for a birthdate and we already discussed that would piss off customers if we required year anyway.

No, This makes no sense. It will result in more space in the database, and will also add unnecessarily any code where you are just looking for the year. You'll have to use DatePart() in each query or extract the year in code. Either approach hurts performance.

But if your boss told you to do it, then you should probably do it. I wouldn't make an issue of it. What's your boss's reasoning?

Correction

I retract the portion of the answer above that says "more space in the database". The performance savings I was referring to are in the conversion to query by year.

Don't over think this. Just use the Date type and be done with it.

NOTE:

Taking into account Ron's answer, storing each date component separately is a step backwards in time. You should really always take advantage of the tools given to you. The Date type is a first class citizen in any .NET language.

Using the date will save space if you have not considered that.

http://msdn.microsoft.com/en-us/library/bb675168.aspx

If there's no reason to put the separate day/month int columns into a Date column, I don't see why an optional year should be stored as a date.

Something makes me want to see the month/day columns as Dates too...but I like the other logic of "don't overthink this" as well...

Personally I'd store all 3 values (month, day, year) as a date in the date field - defaulting the year if they don't enter it. That way you have automatic validation on the months and days being valid ... except for Feb 29 and no year, since the date math won't know if it was a leap year or not.

Storing them as ints means you have to do all that validation yourself.