how do I define a table column for MM/YYYY

Question

how do I define a table column for MM/YYYY

david myers-292056

SSC-Addicted

Points: 413
More actions
January 27, 2006 at 2:13 pm

#166916

How do you suggest I store a field that will store a value of the format month/year (mm/yyyy) , should I create a datetime field - if so how do I specify this format - or should I create a varchar(7) field

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

The "format" of a datetime column is simply a presentation issue. It is just an 8 byte number behind the scenes.

It is usually better to model data in a way that more accurately reflects the real world meaning of the data. I'd have 2 numeric columns, Month and Year.

The presentation of the data in a certain format can be taken care of in the presentation layer.

As soon as you model this as a varchar(7) and get your database built, I can gurantee you someone will come up with a reporting requirement that asks you to perform some sort of date math and then you'll be kicking yourself as you write the string parsing functions that try & figure out which varchar(7) represents 3 months ago.

[Edit] Here's an example of what will happen ...

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=250703

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 2

I agree with PW... store the date as a DateTime field or you'll kick yourself in the near future.

One method to display the date from a DateTime column using the MM/YYYY format is as follows...

SELECT RIGHT(CONVERT(VARCHAR(10),GETDATE(),103),7)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

[KH] SSCommitted Points: 1908 More actions · Answer 3

Definately datetime data type.

As your date time data is only month & year, there are 2 way you can do it.

1. Always store the date as 1st of the month.

2. Store the date time with any day but when retrieving, constrain the result in the where clause.

See example below for illustration.

declare

@mth table

(

idintidentity(1,1),

mthdatetime

)

declare@inputdatetime

insert into @mth

select'2006-01-02'union all

select'2005-12-01'union all

select'2006-01-10'union all

select'2006-02-12'union all

select'2005-12-01'

select@input= '2005-12-01'

-- Convert the @input to 1st of the month

select@input= dateadd(day, -1 * (day(@input) - 1), @input)

-- for date store as 1st of the month

select *

from@mth

wheremth= @input

select@input= '2006-01-04'

-- Convert the @input to 1st of the month

select@input= dateadd(day, -1 * (day(@input) - 1), @input)

-- for date not store as 1st of the month

select *

from@mth

wheremth>= @input

andmth< dateadd(month, 1, @input)