Hi,
We have a number of SP's which have a couple of datetime params.
We expect the format to be such as 'YYYYMMDD' to get around the issue of US
vs UK dates. Unfortunately if an invalid date is passed, it errors straight
away with (e.g.)
Server: Msg 8114, Level 16, State 4, Procedure usp_GetUsage, Line 0
Error converting data type varchar to datetime.
Is there any way of getting around this? The error occurs BEFORE I can check
whether the dates are valid with the isdate() function.
I know that I can change all the params to varchar, and check them before
putting the values into datetime variables, but this seems a bit of a fudge.
Any help would be most appreciated.
Cheers!
AndyWell, you pass a invalid parameter to a stored procedure. Of course the
parameters are checked _before_ the stored procedure itself is executed.
And what is the exact use of checking the parameter in the stored procedure
itself (if you changed the parameter to a character datatype)? The only
thing you could do is raise a more custom error message, but I hope the
programmers that code against this stored procedure are smart enough to work
out the meaning the error message they get now, and implement error handling
or fix bugs on their side accordingly.
Jacco Schalkwijk
SQL Server MVP
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:6D80C811-3ED4-4F2A-8A4F-2D18FCD111ED@.microsoft.com...
> Hi,
> We have a number of SP's which have a couple of datetime params.
> We expect the format to be such as 'YYYYMMDD' to get around the issue of
> US
> vs UK dates. Unfortunately if an invalid date is passed, it errors
> straight
> away with (e.g.)
> Server: Msg 8114, Level 16, State 4, Procedure usp_GetUsage, Line 0
> Error converting data type varchar to datetime.
> Is there any way of getting around this? The error occurs BEFORE I can
> check
> whether the dates are valid with the isdate() function.
> I know that I can change all the params to varchar, and check them before
> putting the values into datetime variables, but this seems a bit of a
> fudge.
> Any help would be most appreciated.
> Cheers!
> Andy|||I agree with what you're saying, but just wanted to write a more robust SP s
o
that i could provide users with a simple error message.
regards,
Andy
"Jacco Schalkwijk" wrote:
> Well, you pass a invalid parameter to a stored procedure. Of course the
> parameters are checked _before_ the stored procedure itself is executed.
> And what is the exact use of checking the parameter in the stored procedur
e
> itself (if you changed the parameter to a character datatype)? The only
> thing you could do is raise a more custom error message, but I hope the
> programmers that code against this stored procedure are smart enough to wo
rk
> out the meaning the error message they get now, and implement error handli
ng
> or fix bugs on their side accordingly.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Andy" <Andy@.discussions.microsoft.com> wrote in message
> news:6D80C811-3ED4-4F2A-8A4F-2D18FCD111ED@.microsoft.com...
>
>|||The applications that access the database should provide the users with a
simple error message. Users really shouldn't call stored procedures directly
IMO.
Jacco Schalkwijk
SQL Server MVP
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:DDAC2AB1-4794-4F79-B467-04EB4E36CDA7@.microsoft.com...
>I agree with what you're saying, but just wanted to write a more robust SP
>so
> that i could provide users with a simple error message.
> regards,
> Andy
> "Jacco Schalkwijk" wrote:
>|||Nothing reasonable you can do except make sure that when you build an
application you don't allow bad dates. There are lots of date controls that
can be purchased, or easily written.
You "could" change your parms to text and check them, but that is not a
great use of processing time. Checking universally known domain values like
this should always be done in the client where the possible 10 milliseconds
to perform the task will not be added to multiple other operations, causing
greater waiting all around. Doing it as the user leaves the date control
puts this work in a better place.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:6D80C811-3ED4-4F2A-8A4F-2D18FCD111ED@.microsoft.com...
> Hi,
> We have a number of SP's which have a couple of datetime params.
> We expect the format to be such as 'YYYYMMDD' to get around the issue of
> US
> vs UK dates. Unfortunately if an invalid date is passed, it errors
> straight
> away with (e.g.)
> Server: Msg 8114, Level 16, State 4, Procedure usp_GetUsage, Line 0
> Error converting data type varchar to datetime.
> Is there any way of getting around this? The error occurs BEFORE I can
> check
> whether the dates are valid with the isdate() function.
> I know that I can change all the params to varchar, and check them before
> putting the values into datetime variables, but this seems a bit of a
> fudge.
> Any help would be most appreciated.
> Cheers!
> Andy
