Passing date as parameter in SP

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)

Passing date as parameter in SP

Author

Topic

gurusamy.senthil
Yak Posting Veteran

65 Posts

Posted - 2006-06-29 : 10:12:07

Hi friends,

Could any one tell me how to pass the date of format mm/dd/yyyy as parameter in stored parameter.

I just want to pass the date format as mm/dd/yyyy

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-29 : 10:15:16

you can :
1. convert to datetime and pass in as datetime data type.

2. declare the paramter as varchar and convert to datetime in the stored procedure

gurusamy.senthil
Yak Posting Veteran

65 Posts

Posted - 2006-06-29 : 10:22:42

I understood and passed like that only. Instead I just want to pass only the date not an time and that also in format mm/dd/yyyy.

I want to pass only date not with time

gurusamy.senthil
Yak Posting Veteran

65 Posts

Posted - 2006-06-29 : 10:42:00

khtan,

I found the solution. thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-29 : 10:42:47

then just pass in as varchar like 06/29//2006 and define the input parameter as varchar and convert to datetime in your stored procedure

gurusamy.senthil
Yak Posting Veteran

65 Posts

Posted - 2006-06-29 : 11:04:22

I have done like this khtan
Sample data sp:

create procedure @RevDate datetime
As
Insert into sample1(Date)
values(Convert(varchar(10),@RevDate,1))

I am getting the exact output I want.
Anything wrong in this khtan.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-29 : 11:14:03

It is a bad idea to store a date in a varchar column. We see posts here every days from people trying to deal with the results of doing this: invalid dates, can't sort on date, index is useless, uses more space than necessary, etc.

Just store it as a datetime, and convert it to a varchar format when you need to.

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2006-06-30 : 01:19:08

If you use:

create procedure @RevDate datetime

and pass the date as a text string "06/29//2006" then SQL Server will make assumptions about the conversion of the date - and they may not be what you intend! and may change with the configuration of the server, if the database is moved to a different server, etc.

you should either:

1) Pass the date ONLY EVER in the format "yyyymmdd" - note there are no hyphens

2) Pass the date to a varchar parameter and make an explicit conversion which specifies the date type conversion you require - .. i.e. mm/dd/yyyy in this case:


create procedure @RevDate varchar(24)
AS
Insert into sample1(MyDateColumn)
values(Convert(datetime, @RevDate, 101))

And use a datetime datatype for your date columns, not varchar - as others have already recommended

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-30 : 02:20:32

quote:
Originally posted by gurusamy.senthil

I have done like this khtan
Sample data sp:

create procedure @RevDate datetime
As
Insert into sample1(Date)
values(Convert(varchar(10),@RevDate,1))

I am getting the exact output I want.
Anything wrong in this khtan.

What is the datatype of Date column in Sample table. If it is varchar datatype use proper DateTime datatype to avoid unneccessary conversions and related problems. Formation should be done in front end application. If you use Front ends like vb, make use of Format Function.

>>I just want to pass the date format as mm/dd/yyyy

You should always pass them in universal format as Kristen suggested. Otherwise the server with DMY date format will treat that as different date

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-06-30 : 04:02:27

"Otherwise the server with DMY date format will treat that as different date"

Its a pet peeve of mind as you know Madhi! ...

... however I think its more scary than you indicate, because if the server is NOT the right date format it will work correctly some of the time - so its then dependant on WHAT test data is used in QA - and a completely avoidable bug could therefore accidentally get into production.

30/06/2006 will blow up on a mm/dd/yyyy system, but 01/07/2006 will be processed just fine (but will be the wrong date, of course)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources