Error Handling in User Defined Functions

Can it be done?  How?

I have created the following function to convert an oddly (customer) formatted date time string into a real datetime data type.  The problem is, if the OldDate is incomplete, the function throws an error.  What I want is for it to return null.

CREATE FUNCTION BadDateToGoodDate
      (@OldDate       varchar(255))
      
RETURNS datetime
AS  

BEGIN

declare @FormattedDate varchar(255)
declare @retval datetime

if not (@OldDate is null) begin
      Set @FormattedDate = SUBSTRING (@OldDate, 1,2) --Month
      Set @FormattedDate = @FormattedDate + '/'
      Set @FormattedDate = @FormattedDate + SUBSTRING (@OldDate, 3,2) --Day
      Set @FormattedDate = @FormattedDate + '/'
      Set @FormattedDate = @FormattedDate + SUBSTRING (@OldDate, 5,4) --Year
      Set @FormattedDate = @FormattedDate + ' '
      Set @FormattedDate = @FormattedDate + SUBSTRING(@OldDate, 10,8) --Time
      set @retval = cast(@FormattedDate as datetime)
      end
else begin
      set @retval = null
end

return @retval
END


(Obviously I'm not a DBA and there may indeed be an even better way of doing this)
LVL 10
ClifAsked:
Who is Participating?
 
lluthienConnect With a Mentor Commented:
well,
if that's the problem.
just add an extra check before the conversion,
because all you do there is getting substrings.

the only thing that can fail that, is a too short string.

so.
check first for stringlength,
if false return null
else
convert
check isdate
if false
return null
else
return cast

in a somewhat cryptic way, but i think you catch the idea..
0
 
lluthienCommented:
check first if it is a date:

if isdate (@FormattedDate)
begin
    set @retval = cast(@FormattedDate as datetime)
else
   set @retval = null
end
0
 
ClifAuthor Commented:
lluthien
The (valid) value will be in the format as such:

05062005 12:56:13

IsDate() does not recognize this as a valid date.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
lluthienCommented:
it will,
if you check AFTER you did the conversion..

so just the Line BEFORE
set @retval = cast(@FormattedDate as datetime)

checking after the conversion should give a valid date,
otherwise the CAST will also fail
0
 
ClifAuthor Commented:
Well yes, *if* it gets past the conversion.  However if, for some odd reason, the text in the field was '05062005' (someone forgot to add the time), the SUBSTRING(@OldDate, 10,8) function will fail throwing an error.  It is this error that I want to trap.
0
 
ClifAuthor Commented:
I guess sometimes one really can't see the forest for all the trees.  :)

Thanks.
0
 
lluthienCommented:
glad to be of service :)

thx for the points
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.