Using SQL Server 2008 Date Data Type to Store Year

views:

125

answers:

Using SQL Server 2008 Date Data Type to Store Year

Does it make any sense to store just a year in 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.

+2 A:

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

ChaosPandion 2009-10-23 19:28:56

it's not overthinking. I've already implemented integer fields, 3 of them. Why put a hack in for the year when you can separate it out into its own int field. Year is optional.

CoffeeAddict 2009-10-23 20:51:02

he is also storing the day and month as ints. I don't feel the down vote is called for.

ChaosPandion 2009-10-23 22:37:25

+1 A:

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

Yoopergeek 2009-10-23 19:29:40

why? then you'd have to fill in fake information into the date. this is what I want to refrain from doing. Why should I create dates with partial data...makes no sense.

CoffeeAddict 2009-10-23 20:51:52

+2 A:

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.

David Stratton 2009-10-23 19:29:50

He likes shortcuts, I do not. He likes to combine things whenever he can and for me that's bad practice. You shouldn't automatically train your brain to do that. That's why we have classes with 1000+ lines of code, because he tends to combine everything and he thinks speration is the opposite...wasted time. Now I have to work in a huge pile of code because of this. So for me, I think very carefully about combining or separating things out. It makes ALL the difference. Any developer who cares about clean code would agree. OOP is not mean to be combined into a witches brew nor is data

CoffeeAddict 2009-10-23 20:54:17

Your wrong, the Date type is 3 bytes while 3 int fields is 12. You will definitely not hurt performance by using the date type.

ChaosPandion 2009-10-23 21:33:56

At the risk of sounding combative and childish... No, you're wrong. (at least partially) You WILL lose performance in doing the conversion every time you need to get just the year. Which do you think will be faster in a table with millions of rows.. Select... Where DatePart(year, SomeDateField) = 2009 or Select... Where SomIntField = 2009? Conversions are expensive. That's why I specifically included the sentence "You'll have to use DatePart() in each query " in my answer.

David Stratton 2009-10-23 21:40:52

But you're right about the size of the fields. I stand corrected there.

David Stratton 2009-10-23 21:47:06

It is debatable whether you would notice a performance difference from calling Year but I voted you back up for correcting your answer.

ChaosPandion 2009-10-23 21:50:04

@ChaosPandion- you could store the year in a smallint and only use 2 bytes if you were that worried about space.

RichardOD 2009-10-23 22:05:03

David, you don't need to use conversions at all to satisfy that query. Why not WHERE SomeDateField >= '20090101' AND SomeDateField < '20091001'? Also, note that SQL Server 2008 has become much smarter about some conversions on the left hand side. See Adam's post about it (which was a response to a post of mine, partially about avoiding the WHERE DATEPART() stuff): http://is.gd/4znv2

Aaron Bertrand 2009-10-24 14:23:07

Sorry I meant < '20100101'

Aaron Bertrand 2009-10-24 14:23:38

+1 A:

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.

Ron

Ron Savage 2009-10-23 21:49:11

+1 - Good point

ChaosPandion 2009-10-23 21:52:07

ansaurus

tags:

views:

answers:

Using SQL Server 2008 Date Data Type to Store Year

related questions