473,320 Members | 2,083 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Storing a month

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.

Jul 23 '05 #1
1 1756
Thomas R. Hummel (to********@hotmail.com) writes:
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 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_rule 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(datetime, month + '01')


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Robert | last post by:
I have no problem storing dates + times in a System.DateTime object. In addition, it's easy to output a Time as a string from an existing Date/Time. But I'm having trouble storing a time only. ...
1
by: Thomas R. Hummel | last post by:
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...
2
by: David Garamond | last post by:
What would be the more proper way of storing birthday data? It will be used to send out birthday messages for customers ("Happy 30th birthday, Sam!"). But the date of birth is not necessarily known...
4
by: IkBenHet | last post by:
Hello, I am working on an ASP.NET based website project. Without going into much detail; One of the function on this website will be the possibility to upload images via a ASP.NET form to the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.