Invalid dates passed to an SP

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

interval dates

i want to create a user-defined function that accepts 2 dates and returns
the time difference in days, hours, minutes, and seconds-
for example, a possible result would be something like:
2 days, 17 hours, 46 minutes, 12 seconds
has anyone out there done this before in sql server? any help would be much
appreciated.
thanks,
jtjtl
CREATE FUNCTION dbo.dates_range
(
@.date1 DATETIME,
@.date2 DATETIME
)
RETURNS VARCHAR(32)
AS
BEGIN
DECLARE @.sD INT, @.sR INT, @.mD INT, @.mR INT, @.hR INT
SET @.sD = DATEDIFF(SECOND, @.date1, @.date2)
SET @.sR = @.sD % 60
SET @.mD = (@.sD - @.sR) / 60
SET @.mR = @.mD % 60
SET @.hR = (@.mD - @.mR) / 60
RETURN CONVERT(VARCHAR, @.hR)
+':'+RIGHT('00'+CONVERT(VARCHAR, @.mR), 2)
+':'+RIGHT('00'+CONVERT(VARCHAR, @.sR), 2)
END
"JTL" <jliautaud@.hotmail.com> wrote in message
news:uo90dQFDGHA.2908@.TK2MSFTNGP09.phx.gbl...
>i want to create a user-defined function that accepts 2 dates and returns
>the time difference in days, hours, minutes, and seconds-
> for example, a possible result would be something like:
> 2 days, 17 hours, 46 minutes, 12 seconds
> has anyone out there done this before in sql server? any help would be
> much appreciated.
> thanks,
> jt
>|||thanks!
do you know how i can return days as well?
jtl
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23rZKdUFDGHA.1312@.TK2MSFTNGP09.phx.gbl...
> jtl
> CREATE FUNCTION dbo.dates_range
> (
> @.date1 DATETIME,
> @.date2 DATETIME
> )
> RETURNS VARCHAR(32)
> AS
> BEGIN
> DECLARE @.sD INT, @.sR INT, @.mD INT, @.mR INT, @.hR INT
> SET @.sD = DATEDIFF(SECOND, @.date1, @.date2)
> SET @.sR = @.sD % 60
> SET @.mD = (@.sD - @.sR) / 60
> SET @.mR = @.mD % 60
> SET @.hR = (@.mD - @.mR) / 60
> RETURN CONVERT(VARCHAR, @.hR)
> +':'+RIGHT('00'+CONVERT(VARCHAR, @.mR), 2)
> +':'+RIGHT('00'+CONVERT(VARCHAR, @.sR), 2)
> END
>
>
> "JTL" <jliautaud@.hotmail.com> wrote in message
> news:uo90dQFDGHA.2908@.TK2MSFTNGP09.phx.gbl...
>|||You could do something like this with my TTimeSpan UDT at [0]:
declare @.ts TTimeSpan
set @.ts = TTimeSpan::FromDates('1/1/2005 2:00', '1/12/2005 14:6')
select @.ts.ToString(), @.ts.ToLongString()
Output:
-- ---
11.12:6:0 11 Days 12 Hours 6 Minutes 0 Seconds 0 Milliseconds
[0] - http://channel9.msdn.com/ShowPost.aspx?PostID=147390
William Stacey [MVP]
"JTL" <jliautaud@.hotmail.com> wrote in message
news:%23APwXiFDGHA.2700@.TK2MSFTNGP14.phx.gbl...
> thanks!
> do you know how i can return days as well?
> jtl
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23rZKdUFDGHA.1312@.TK2MSFTNGP09.phx.gbl...
>|||i didn't see where to get the source for TTimeSpan- can you help?
jt
"William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
news:uctoCXQDGHA.2436@.TK2MSFTNGP15.phx.gbl...
> You could do something like this with my TTimeSpan UDT at [0]:
> declare @.ts TTimeSpan
> set @.ts = TTimeSpan::FromDates('1/1/2005 2:00', '1/12/2005 14:6')
> select @.ts.ToString(), @.ts.ToLongString()
> Output:
> -- ---
--
> 11.12:6:0 11 Days 12 Hours 6 Minutes 0 Seconds 0 Milliseconds
> [0] - http://channel9.msdn.com/ShowPost.aspx?PostID=147390
> --
> William Stacey [MVP]
> "JTL" <jliautaud@.hotmail.com> wrote in message
> news:%23APwXiFDGHA.2700@.TK2MSFTNGP14.phx.gbl...
>|||JTL (jliautaud@.hotmail.com) writes:
> i didn't see where to get the source for TTimeSpan- can you help?
What about reading William's post in full?
> "William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
> news:uctoCXQDGHA.2436@.TK2MSFTNGP15.phx.gbl...
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx