Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 2 of 2 total
Thread cast date + varchar field to timestamp with catching errors?
Mon, Nov 7 2022 10:56 AMPermanent Link

Yusuf Zorlu

MicrotronX

Hi,

let's say I have following query:

select
  cast(
     cast(mydatefield as varchar)+' '+
     cast(mytimecharfield as varchar)
  as timestamp) as mydatetime
from mytable

If the mytimecharfield has a valid value, everything is ok. But
sometimes we have a field where the user enters some text like 'none'.
Is there a way to catch such errors and generate date + '00:00'
directly with elevatedb-sql?

--
--
Yusuf Zorlu | MicrotronX
Tue, Nov 8 2022 12:06 AMPermanent Link

Yusuf Zorlu

MicrotronX

I solved it by creating a FUNCTION:

CREATE FUNCTION "mxDateplustime2TimeStamp" (IN "InputDate" DATE, IN
"InputTime" varchar)
RETURNS TIMESTAMP
BEGIN            
  declare mytime time;
  begin
     set mytime=cast(InputTime as time);
  exception                             
     set mytime=cast('00:00' as time);
  end;                                

  RETURN cast( cast(InputDate as varchar)+' '+cast(mytime as varchar)
as timestamp);
END
DESCRIPTION 'Convert Date + Varchar to Timestamp and catch errors'
VERSION 1.00

--
--
Yusuf Zorlu | MicrotronX
Image