Storing month and year

[Terri]

I need to store month and year in a table. I'm not concerned about date or
time. Should I still use a date/time field? Should I use 2 separate char
fields? Calendar table? Looking for best practices that would facilitate
validating data input and querying?

[JT]

Seperate columns for month and year would simplify querying, and using
integers instead of characters would conserve disk and memory storage.

For example, the following would use only 2 bytes, but support only a 2
digit year:
period_month tinyint,
period_year tinyint,

The following combination would use 3 bytes and support a 4 digit year:
period_month tinyint,
period_year smallint,

For comparison, smalldatetime would be 4 bytes and datetime would be 8
bytes.

"Terri" <te...@cybernets.com> wrote in message
news:ddvkka$fep$1...@reader2.nmix.net...

[Raymond D'Anjou]

If you only need year and month, I would lean towards storing this in 2
columns (year = smallint, month = tinyint)
Defaults can be set up to limit what can be entered (1-12 for months and
2000-2100 for years).
A modified calendar table can also be used with data in the table looking
like this:
Year Month (of course, change the names to something more
representative)
2000 1
2000 2
...
2100 12
with foreign key references to your table.
You would not need the defaults in this case and this could facilitate
reporting/querying.

"Terri" <te...@cybernets.com> wrote in message
news:ddvkka$fep$1...@reader2.nmix.net...

[Andrew J. Kelly]

Actually I am going to go the opposite route of the other suggestions. I am
assuming you will always search by Year and Month together. If this is true
then I would use a single INT column and store it as such:

March 2005 = 200503

This makes the use of an index on the single column searchable and
efficient. It also means you can sort by this as well.

--
Andrew J. Kelly SQL MVP

"Terri" <te...@cybernets.com> wrote in message
news:ddvkka$fep$1...@reader2.nmix.net...

[Raymond D'Anjou]

Would this method be as efficient for, let's say for 1 example, grouping on
Years?

"Andrew J. Kelly" <sqlmvpn...@shadhawk.com> wrote in message
news:eNFxgV0o...@TK2MSFTNGP15.phx.gbl...

[AK]

if you are looking for a perfect solution, there is no such thing.
If you want your code to be easy to understand/maintain, use a standard
smalldatetime column.
If you have strong reasons to use one or 2 integer columns, make sure
there are check or FK constraints to validate, checks beign usually way
faster.
If you really need to save bytes, a smallint or even a tinyint might
suffice if you start numbering months from some date and number them
sequentially with no gaps, for instance
0 - January 2001
1 - Feb 2001
(snip)
11 - Dec 2001
12 - Jan 2002

[Andrew J. Kelly]

Depends on how you do it and what your indexes are like. I don't know what
your table looks like and how you will use it so it is hard to say. But
basically yes it should be. If you use a BETWEEN or <> in the WHERE clause
it should be able to use the index appropriately, especially if the
clustered index in on this column.

--
Andrew J. Kelly SQL MVP

"Raymond D'Anjou" <rda...@savantsoftNOSPAM.net> wrote in message
news:OQbX080...@TK2MSFTNGP14.phx.gbl...

[JT]

Sure, if you don't mind violating seventh normal form. ;-)

"Andrew J. Kelly" <sqlmvpn...@shadhawk.com> wrote in message
news:eNFxgV0o...@TK2MSFTNGP15.phx.gbl...

[Raymond D'Anjou]

I'm not the poster but I'll accept the answer. :-)
The best solution probably depends on information we don't have.
Tables, relationships, reporting needs.

"Andrew J. Kelly" <sqlmvpn...@shadhawk.com> wrote in message

news:u0%23NCb1o...@TK2MSFTNGP09.phx.gbl...

[Raymond D'Anjou]

"AK" <AK_TIRE...@hotmail.COM> wrote in message
news:1124301921.5...@o13g2000cwo.googlegroups.com...

> if you are looking for a perfect solution, there is no such thing.

My wife would probably disagree. :-(
Then again, she IS my perfect solution (she told me to say that).

[AK]

so you see her as a solution, not as a perpetual cause of problems -
good for both of you ;)

[ML]

Whichever solution that isn't based on a datetime data type you choose,
remember: there are only twelve months in any given year. So a check
constraint would be nice (ta say the least).

ML

[--CELKO--]

Rows are not records; fields are not columns.Fields have no data type,
constraints, DRI, etc. -- their meaning comes from the application
program reafding them. Time is a continuum and should be modeled with
a start and end datetime column. The two columns together are atomic
(see some articles I wrote in www.dbazine.com).

You can put the two columns in the table or have them in a calendar
table depending on how you will use these monthly ranges. A simple
BETWEEN preidcate will do most of your work and allow you to use
indexes.

Never store them as CHAR(n) or numerics -- you will waste too much time
converting them to temporal data types. The constraints will be messy
and DRI will be complex.

As an aside, I am getting soft on the MySQL convention of zeroes for
durations -- '2005-01-00' is all of January and '2005-00-00' means all
of 2005. Unfortunately, we cannot carry this down to hours, minutes
and seconds.

[JT]

After all that, you forgot to include to amazon link to your latest book.

"--CELKO--" <jcel...@earthlink.net> wrote in message
news:1124314488.0...@o13g2000cwo.googlegroups.com...

[ML]

"...the MySQL convention of zeroes for

durations -- '2005-01-00' is all of January and '2005-00-00' means all

of 2005..."

Are they serious?

Does '0000-00-00' stand for 'all eternity'?

ML

[Andrew J. Kelly]

Sorry Raymond. I just looked at it as a reply and not the name.

--
Andrew J. Kelly SQL MVP

"Raymond D'Anjou" <rda...@savantsoftNOSPAM.net> wrote in message

news:OV6BLr1o...@TK2MSFTNGP14.phx.gbl...

[Robert Klemme]

If you need to do calculations (add 1 month, substract 5 years) then I'd
probably choose the date type anyway because SQL Server has them
implemented already. You could use them with integer columns also but
then you need a conversion to date and back.

Kind regards

robert

[--CELKO--]

How about (-9999-01-01, +9999-12-31), since that is the range for
ISO-8601 dates? Seriously, I honestly do not know, but it might be
handy. I keep using a NULL as a marker for a event that is still in
progress and I feel bad about it because I do have some information
about that moving point in time. But there is a diffrence between "all
eternity" and NULL.