In the future DDF (target to be released with Elliott 8.3,), we will add the following four user-defined functions:

• ELIDATE2SQL
  
• SQLDATE2ELI
  
• ELITIME2SQL
  
• SQLTIME2ELI
  

For example, you can use the following SELECT statement:
                select order_no, ELIDATE2SQL(order_date) from cpordhdr;
The date return in the above sample statement will be in the native SQL date format, instead of the Elliott 8-digit date.

The following is the detail of the script that we use to create these four user-defined functions:

CREATE FUNCTION ELIDATE2SQL(:ELIDATE NUMERIC(8,0))
RETURNS DATE
AS 
BEGIN
DECLARE :C CHAR(8);
DECLARE :D DATE;
IF (:ELIDATE = 0) THEN
  SET :D=NULL;
ELSE
  SET :C=CONVERT(:ELIDATE,SQL_CHAR);
  SET :D=CONVERT(concat(concat(concat(concat(left(:C,4),'-'),substring(:C,5,2)),'-'),right(:C,2)),SQL_DATE);
END IF;
RETURN :D;
END;
 
CREATE FUNCTION SQLDATE2ELI(:SQLDATE DATE)
RETURNS NUMERIC(8,0)
AS 
BEGIN
DECLARE :C CHAR(10);
DECLARE :Y NUMERIC(4,0);
DECLARE :M NUMERIC(2,0);
DECLARE :D NUMERIC(2,0);
DECLARE :N NUMERIC(8,0);
IF (:SQLDATE = NULL) THEN
  SET :N=0
ELSE
  SET :C=CONVERT(:SQLDATE,SQL_CHAR);
  SET :Y=CONVERT(LEFT(:C,4),SQL_NUMERIC);
  SET :M=CONVERT(SUBSTRING(:C,6,2),SQL_NUMERIC);
  SET :D=CONVERT(RIGHT(:C,2),SQL_NUMERIC);
  SET :N=:Y * 10000 + :M * 100 + :D;
END IF;
RETURN :N;
END;
 
CREATE FUNCTION ELITIME2SQL(:ELITIME NUMERIC(6,0))
RETURNS TIME
AS 
BEGIN
DECLARE :C VARCHAR(12);
DECLARE :T TIME;
SET :C=CONVERT(:ELITIME,SQL_CHAR);
-- PAD ZEROES ON THE LEFT
SET :C=RIGHT(CONCAT('000000',:C),6);
SET :T=CONVERT(concat(concat(concat(concat(left(:C,2),':'),substring(:C,3,2)),':'),right(:C,2)),SQL_TIME);
RETURN :T;
END;
 
CREATE FUNCTION SQLTIME2ELI(:SQLTIME TIME)
RETURNS NUMERIC(6,0)
AS
BEGIN
DECLARE :C CHAR(8);
DECLARE :H NUMERIC(2,0);
DECLARE :M NUMERIC(2,0);
DECLARE :S NUMERIC(2,0);
DECLARE :N NUMERIC(6,0);
IF (:SQLTIME=NULL) THEN
  SET :N=0;
ELSE
  SET :C=CONVERT(:SQLTIME,SQL_CHAR);
  SET :H=CONVERT(LEFT(:C,2),SQL_NUMERIC);
  SET :M=CONVERT(SUBSTRING(:C,4,2),SQL_NUMERIC);
  SET :S=CONVERT(RIGHT(:C,2),SQL_NUMERIC);
  SET :N=:H * 10000 + :M * 100 + :S;
END IF;
RETURN :N;
END; 

EMK