Storing a month

[Thomas R. Hummel]

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.

[Erland Sommarskog]

Re: Storing a month

Thomas R. Hummel (tom_hummel@hot mail.com) writes:[color=blue]
> 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.[/color]

I had a case recently in our system where we had a lot of things to go
by month. I decided to store the months as char(6), using a user-defined
type, that I constraint to be a legal data with 01 added:

EXEC sp_addtype 'aba_yearmonth' , 'char(6)'
go
CREATE RULE aba_yearmonth_r ule AS isdate(@x + '01') = 1 OR @x IS NULL
go
EXEC sp_bindrule 'aba_yearmonth_ rule', 'aba_yearmonth'
go

Microsoft has deprecated the use of CREATE RULE, but they have not
supplied anything else that matches this functionality for types.

Note that with this format you can easily use the datetime functions:

select datediff(mm, col + '01', col2 + '01')

If you think that is a little kludgy, you could add computed columns
to parallel each month column:

month aba_yearmonth NOT NULL,
monthasdate AS convert(datetim e, month + '01')






--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp