pinkman
asked on
error handling in functions
hi .
stringdate is a varchar column which contains date&time information in various formats.
i want to transform this info into sql date format.
i built user defined functions to convert all the
formats that exist now but new formats may appear anytime.
my query look like this :
update temp_prod set mydate=dbo.getmydate(strin
the function should look like this :
create function getmydate (@string varchar(20))
returns smalldatetime
.....
--step1:do some checks and changes on the input string
--into @new_string
set @date=cast(@new_string as smalldatetime)
/*
for example suppose @new_string='Friday02/28/0
which is invalid date (step1 failed to delete the
'friday' prefix), now the function need to
set @date='01/01/2000'
and the update query to end successfully.
then whenever mydate='01/01/2000' it will mean
i have a date format problem.
*/
handling this problem by opening a cursor
for the update will be slower maybe too slow
for me.
save me and get my gratitude.
stringdate is a varchar column which contains date&time information in various formats.
i want to transform this info into sql date format.
i built user defined functions to convert all the
formats that exist now but new formats may appear anytime.
my query look like this :
update temp_prod set mydate=dbo.getmydate(strin
the function should look like this :
create function getmydate (@string varchar(20))
returns smalldatetime
.....
--step1:do some checks and changes on the input string
--into @new_string
set @date=cast(@new_string as smalldatetime)
/*
for example suppose @new_string='Friday02/28/0
which is invalid date (step1 failed to delete the
'friday' prefix), now the function need to
set @date='01/01/2000'
and the update query to end successfully.
then whenever mydate='01/01/2000' it will mean
i have a date format problem.
*/
handling this problem by opening a cursor
for the update will be slower maybe too slow
for me.
save me and get my gratitude.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
dear curtis591 , this probably gonne solve this problem.
thanks.
still if there is a way ,for now it is only for academic reasons , to get an error within a function , because
the function is not handling all cases , and still
make an update/select using a default value whenever
there's an error
thanks.
still if there is a way ,for now it is only for academic reasons , to get an error within a function , because
the function is not handling all cases , and still
make an update/select using a default value whenever
there's an error
I haven't done a whole lot of writing functions and returning an error message. Off the top of my head (not sure if you can do this or not. When the date is invalid return the 01/01/2000 and also do an insert of the invalid parameter passed into the function in an error table???
ex
if isdate(@newstring) = 1
set @date=cast(@new_string as smalldatetime)
else
begin
insert into errors (bad_parameter)
select @string
set @date=cast('01/01/2000' as smalldatetime)
end
That is probably what I would try again I am not really a 100% sure if it would work or not without trying.
ex
if isdate(@newstring) = 1
set @date=cast(@new_string as smalldatetime)
else
begin
insert into errors (bad_parameter)
select @string
set @date=cast('01/01/2000' as smalldatetime)
end
That is probably what I would try again I am not really a 100% sure if it would work or not without trying.
I'm sure you can do something like
set @date=cast(@new_string as smalldatetime)
If @@Error <> 0
goto Err:
Err:
set @date='01/01/2000'
I'm not too sure about it exactly, but i have done it before, i'll check at work tomorrow and let you know.
Matt.
set @date=cast(@new_string as smalldatetime)
If @@Error <> 0
goto Err:
Err:
set @date='01/01/2000'
I'm not too sure about it exactly, but i have done it before, i'll check at work tomorrow and let you know.
Matt.
try this
set @date=cast(@new_string as smalldatetime)
If @@Error <> 0
goto Err
and any other checks you have for your date
If @@Error <> 0
goto Err --Stick this after each date check
goto CleanUp
Err:
set @date='01/01/2000'
CleanUp:
Matt.
set @date=cast(@new_string as smalldatetime)
If @@Error <> 0
goto Err
and any other checks you have for your date
If @@Error <> 0
goto Err --Stick this after each date check
goto CleanUp
Err:
set @date='01/01/2000'
CleanUp:
Matt.
pinkman:
This old question needs to be finalized -- accept an answer, split points, or get a refund. For information on your options, please click here-> http:/help/closing.jsp#1
EXPERTS:
Post your closing recommendations! No comment means you don't care.
This old question needs to be finalized -- accept an answer, split points, or get a refund. For information on your options, please click here-> http:/help/closing.jsp#1
EXPERTS:
Post your closing recommendations! No comment means you don't care.
ASKER
thanks.
still if there is a way ,for now it is only for academic reasons , to get an error within a function , because
the function is not handling all cases , and still
make an update/select using a default value whenever
there's an error