473,804 Members | 2,124 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 1785
Thomas R. Hummel (to********@hot mail.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_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, es****@sommarsk og.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
1816
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. Is there any way to store only a time without the date portion (like 3:00 am) in this data structure? Or, if not, is there another data structure that would be preferable? Thanks
1
371
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 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...
2
2490
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 (in which case, we will only send out "Happy birthday, Sam!"). I prefer using the builtin date type instead of three smallints. But I don't like having to arbitrarily set, say, year 1000 AD or 1 BC to represent "unknown year". Any thoughts?
4
4647
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 webserver. These images can then be shown on to webpages throughout the site. What would be the best way of storing these images? Just by saving them in a folder on de webserver or in a database?
0
9715
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10603
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10099
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9176
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7643
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6869
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5675
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4314
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
3836
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.