Storing smalldatetime data that comes broken

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)

Storing smalldatetime data that comes broken

Author

Topic

cesark
Posting Yak Master

215 Posts

Posted - 2005-08-25 : 04:41:14

Hi,

I receive from the web application a date divided in three parts (day, month and year) because the user enter the day in a field (a ddl), the month in another field (a ddl) and the year in another field (text field). In order to store this three parts in one smalldatetime column in the DB I wrote this SP:


USE market
GO
ALTER PROCEDURE new_offer
@day char(2) = null, @month char(2) = null, @year char(4) = null
As

Declare @Date As smalldatetime
SET @Date = @day + '/' + @month + '/' + @year

SET NOCOUNT ON

	INSERT INTO Offers (Date)
	VALUES (@Date)
        
SET NOCOUNT OFF        
GO

Is this a good way to store a date sent by the app in that way(in three parts)?

Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-08-25 : 05:48:41

it would be better to store it in yyyymmdd format.
so:
SET @Date = @year + @month + @day

Go with the flow & have fun! Else fight the flow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-25 : 06:11:27

Because all the datatypes are chars
SET @Date = @year + @month + @day
will produce 20002 2 if year, month and day are 2000, 2 and 2

So better to format it as

SET @Date = @year +'-'+ @month +'-'+ @day

Madhivanan

Failing to plan is Planning to fail

cesark
Posting Yak Master

215 Posts

Posted - 2005-08-25 : 07:09:26

Ok thank you. I will do it thus:


USE market
GO
ALTER PROCEDURE new_offer
@day varchar(2) = null, @month varchar(2) = null, @year varchar(4) = null
As

Declare @theDate As smalldatetime
SET @theDate = @year +'-'+ @month +'-'+ @day 
SET NOCOUNT ON

	INSERT INTO Offers (theDate)
	VALUES (the@Date)
        
SET NOCOUNT OFF        
GO

Perhaps it' s better varchar(2) instead of char(2) since I don' t know what is the format the app sends, I only know that sends for example as a day and month: '01, 02, 03, 04, 10, 11' format (with leading 0 if necessary).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources