Deciding what data type to use in two columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Deciding what data type to use in two columns

Author

Topic

cesark
Posting Yak Master

215 Posts

Posted - 2005-08-24 : 10:47:39

My web application sends to the DB day, month and year of an order, and I store that date in three different columns: Day, Month and Year. The year column data type is smallint in order to store 2005, 2006,.., 2025 format. In the Day and Month columns I want to store the format 01, 05, 09, 10, 12,.. for each one. What is the best data type I can use?

Thank you,
Cesar

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-24 : 10:53:53

You can still use smallint data type and show them in presentation layer by formating that number

Madhivanan

Failing to plan is Planning to fail

cesark
Posting Yak Master

215 Posts

Posted - 2005-08-24 : 11:27:21

Hi,

Do you think this way of storing the date is a good system (In three separate columns)?

Thanks!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-24 : 12:49:58

>>Do you think this way of storing the date is a good system (In three separate columns)?

In my opinion, no. If the concept you are trying to capture is a date, then I think you should use a single column. either smalldatetime or datetime. You can always format that in anyway you want in the presentation layer as Madhivanan suggested. The advantage of maintaining a precice date and being able to use all the datetime functionality that sql server provides is a big thing.

Be One with the Optimizer
TG

cesark
Posting Yak Master

215 Posts

Posted - 2005-08-24 : 13:24:40

In my app I wanted to let user specify only month and year, or even only the year (21/01/2005 or 01/2005 or 2005). It would be possible to store these three formats with only one smalldatetime column?

This is the reason why I decided to store that date in three columns. Anyway perhaps I have to consider force the user to enter the complete date or none..

(Indeed, this date indicates the date of harvest of an agricultural product)

What do you think?

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-24 : 13:48:13

Why?

So they eneter a month. You want data for the last decade?

Anyway, use datetime, and use the DATEPART function


DECLARE @x datetime
SELECT @x = '2005-05-24'
SELECT DATEPART(month,@x)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources