Microsoft SQL Server forum - Storing a month

Author

Storing a month

Thomas R. Hummel

2005-04-26, 1:23 pm

Hello all,

I have been working with several databases here that are basically data
marts. A lot of the entities have an attribute that is a particular
year and month. For example, a financial transaction may be posted for
a particular month, regardless of the actual date on which it occurred.
In this system, these year/month combinations have typically been
stored as integers of the form YYYYMM. My question is, how have others
stored this type of information and what advantages/disadvantages have
you found to your method?

The problem that I have found with the current method is that you
cannot easily find the difference between two of these dates. For
example, 200401 - 200312 = 89 (not 1). Storing the values as datetimes
(using the first of the month) allows for DATEDIFF(mm, '2003-12-01',
'2004-01-01') = 1. Of course, a little extra (and meaningless) data is
being stored. In case the table sizes makes a difference here due to
the extra data being stored, we are usually talking about over 100M
rows.

I don't like the idea of storing the values in two columns (year and
month) because that does nothing to improve on the ability to perform
useful functions on the values and very importantly, the month really
has no meaning without the year, so I don't think that it should be
stored by itself.

Thanks for any advice/insight.

-Tom.

Hugo Kornelis

2005-04-26, 8:23 pm

On 26 Apr 2005 11:09:26 -0700, Thomas R. Hummel wrote:

>Hello all,
>
>I have been working with several databases here that are basically data
>marts. A lot of the entities have an attribute that is a particular
>year and month. For example, a financial transaction may be posted for
>a particular month, regardless of the actual date on which it occurred.
>In this system, these year/month combinations have typically been
>stored as integers of the form YYYYMM. My question is, how have others
>stored this type of information and what advantages/disadvantages have
>you found to your method?
>
>The problem that I have found with the current method is that you
>cannot easily find the difference between two of these dates. For
>example, 200401 - 200312 = 89 (not 1). Storing the values as datetimes
>(using the first of the month) allows for DATEDIFF(mm, '2003-12-01',
>'2004-01-01') = 1. Of course, a little extra (and meaningless) data is
>being stored. In case the table sizes makes a difference here due to
>the extra data being stored, we are usually talking about over 100M
>rows.
>
>I don't like the idea of storing the values in two columns (year and
>month) because that does nothing to improve on the ability to perform
>useful functions on the values and very importantly, the month really
>has no meaning without the year, so I don't think that it should be
>stored by itself.
>
>Thanks for any advice/insight.
>
> -Tom.

Hi Tom,

If you choose the datetime format, you can use smalldatetime (assuming you
don't need to store data before January 1900 or after June 2079). This
takes the same amount of storage as one integer, and it has the advantage
of being able to use date functions (as you already indicate).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Sponsored Links