Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2004
    Posts
    19

    Unanswered: Create function problem

    Hi

    i know this question is pretty trivial, but i am struggling on the create function command.
    I want to create a function which takes in a column of datetimes and spits out a column of strings (some operations performed on the dates that are returned)

    I have looked and looked but i cant seem to get the syntax down correctly.
    Could someone PLEASE provide me with a template. Heres the sql code that will go in the function:

    VARCHAR(DATE(t1)) CONCAT ' ' CONCAT
    VARCHAR(CHAR(TIME(t1), JIS)) CONCAT '.' CONCAT
    LTRIM(RTRIM(LEFT(VARCHAR(CHAR(MICROSECOND(t1))), 3))) CONCAT
    REPEAT('0', 3 - LENGTH(LTRIM(RTRIM(LEFT(VARCHAR(CHAR(MICROSECOND(t 1))), 3)))))

    Thank you

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Try
    CREATE FUNCTION Myschema.myudf(t1 timestamp)
    RETURNS VARCHAR(75) LANGUAGE SQL DETERMINISTIC NO EXTERNAL ACTION CALLED ON NULL INPUT
    BEGIN ATOMIC
    DECLARE OUTTEXT VARCHAR(75);

    SET OUTTEXT = VARCHAR(DATE(t1)) CONCAT ' ' CONCAT
    VARCHAR(CHAR(TIME(t1), JIS)) CONCAT '.' CONCAT
    LTRIM(RTRIM(LEFT(VARCHAR(CHAR(MICROSECOND(t1))), 3))) CONCAT
    REPEAT('0', 3 - LENGTH(LTRIM(RTRIM(LEFT(VARCHAR(CHAR(MICROSECOND(t 1))), 3)))));

    RETURN OUTTEXT;
    END

    HTH

    Andy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •