Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Disco Patrick
Starting Member
18 Posts |
 Posted - 2009-10-15 : 05:57:33
|
| What is best practice when storing a date when you only require YYYY or MMYYYY ?Someone on the team is suggesting we store it as a char, so we don't store the redundant DD section. I'm uneasy with this approach. It means we lose the functionality associated with the date type. It also forces us to store the date in the format YYYY/MM so that we can order results correctly. It also allows for incorrect data to be stored in the field, e.g. '2009/99'. It doesn't allow for the fact that in the future we may need to store the DD part as requirements change.But he is adamant that we must not store any redundant data. He also has a good point that we should not be making up data for the DD part, e.g. defaulting to '01'.What should we do? Is there some way of configuring the date type to mark the DD part as irrelevant? |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-10-15 : 06:14:09
|
It is always a good idea not to store redundant data!But in case of a date I never would talk about redundant data.Store the complete date in the right data type and you never will have problems to filter your data.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-10-15 : 06:17:21
|
if you only required the year, then store in integerif year & month, use data type date and always set 1 as the day. It is much easier if you need to perform any operations on the Year+Month. For example to get the date Year+Month date for 15 month ago, only need a simple dateadd(month, -15, [datecol]).
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2009-10-15 : 07:03:45
|
| I'd store it in several columns actually...one datetime column and then additional int columns for year and month. More data but storage is not *that* big of a problem...- Lumbagohttp://xkcd.com/327/ |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-10-15 : 07:07:23
|
if it is a datetime column and 2 separate integer column, what if somebody changed the integer column value to something else ?You can use a datetime column for the Year+Month and 2 compute column for separate year and month using datepart()
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
|
|
|
|
|
date type when only the year is required