[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 310
  • Last Modified:

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)
0
Clif
Asked:
Clif
  • 4
  • 3
1 Solution
 
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
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
lluthienCommented:
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
 
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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now