Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2014
    Posts
    294

    Unanswered: Date function output varies

    Hi,

    I have written one function.

    CREATE OR REPLACE FUNCTION LI_DATE (p_date DATE)
    RETURNS DATE
    LANGUAGE SQL
    NO EXTERNAL ACTION
    F1: BEGIN

    DECLARE l_date DATE;
    DECLARE l_month VARCHAR(20);
    DECLARE l_day INTEGER;
    declare h_date date;


    SET l_date = TO_DATE('1900-01-01','YYYY-MM-DD');
    ----SET l_date = TO_DATE('01/01/1900','MM/DD/YYYY');

    SET l_month = NULL;
    SET l_day = NULL;

    IF p_date IS NOT NULL
    THEN

    --fetch the month to be added to the date passed as in parameter
    SET l_month = (SELECT VALUE FROM DGTY WHERE NAME = 'afjk67');

    --fetch the month to be added to the date passed as in parameter
    SET l_day = (SELECT VALUE FROM DGTY WHERE NAME = 'tiiq87');

    -
    SET l_date = (SELECT ADD_MONTHS(p_date,l_month) FROM SYSIBM.SYSDUMMY1);

    ---SET l_date = (select cast(l_date as varchar(10)) from sysibm.sysdummy1);
    SET l_date = (SELECT (DATE(l_date) + l_day) FROM SYSIBM.SYSDUMMY1);


    --date value is returned


    RETURN DATE(l_date);
    ELSE

    SET l_date = p_date;
    RETURN DATE(l_date);
    END IF;

    ---RETURN temp;
    END

    This is my input value : 12/10/1939
    my output value is : 1938-12-12 00:00:00.0

    I am expecting my output has to be in the same format , how i gave my input as DD/MM/YYYY.

    tried with VARCHAR_FORMAT too. But not able to convert my output.
    Can some plz let me how to do this

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    The display format of a DB2 DATE object varies with the locale settings of the client that is performing the display action.
    Additionally for DB2 V10.5 , the storage format (and display format) of DATE depends on the value of DB2_COMPATIBILITY_VECTOR at time of database creation (db cfg date compatibility ).
    If you want to explicitly specify the format, either use timestamp_format() or to_char()/varchar_format() on the scalar result of the udf.
    If you write, "tried with VARCHAR_FORMAT too. But not able to convert my output" it tells us nothing, so post exactly what you did, and the result.
    If you want your code to work properly in multiple locales, best to use ISO format for dates.

  3. #3
    Join Date
    Jul 2014
    Posts
    294
    Thanks for the brief description.

    This is the code which I tried

    CREATE OR REPLACE FUNCTION LI_DATE (p_date DATE)
    RETURNS DATE
    LANGUAGE SQL
    NO EXTERNAL ACTION
    F1: BEGIN

    DECLARE l_date DATE;
    DECLARE l_month VARCHAR(20);
    DECLARE l_day INTEGER;
    declare h_date date;



    SET l_month = NULL;
    SET l_day = NULL;

    IF p_date IS NOT NULL
    THEN

    --fetch the month to be added to the date passed as in parameter
    SET l_month = (SELECT VALUE FROM DGTY WHERE NAME = 'afjk67');

    --fetch the month to be added to the date passed as in parameter
    SET l_day = (SELECT VALUE FROM DGTY WHERE NAME = 'tiiq87');

    --adds a negative value to the month and then adds a day to the value


    set l_date = (SELECT VARCHAR_FORMAT(ADD_MONTHS('19/09/1949',10),'DD/MM/YYYY') FROM SYSIBM.SYSDUMMY1);
    call dbms_output.put_line(l_date);

    SET l_date = (SELECT VARCHAR_FORMAT((DATE(l_date) + l_day),'DD/MM/YYYY') FROM SYSIBM.SYSDUMMY1);
    call dbms_output.put_line(l_date);


    RETURN l_date;
    call dbms_output.put_line(l_date);

    ELSE

    --SET l_date = p_date;

    RETURN l_date;

    END IF;

    ---RETURN temp;
    END



    still My output is not in the desired format as dd/mm/yyyy.

    I have seen my date compatibility variable under db cfg .

    Number compatibility = ON
    Varchar2 compatibility = ON
    Date compatibility = ON

    how can i turn off this date compatibility. As it is turned on i am getting the output along with zeros for time as,
    1938-12-12 00:00:00.0.

    If i turn off the date compatibility then it will show in dd/mm/yyyy format. I have checked on other system where date compatibility is off.
    Last edited by HABBIE; 02-10-15 at 07:32.

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    is the procedure executed at client/server ?
    if client : client has his own settings -- each client can have a specific/different setting
    it is always better to force than giving the choice..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  5. #5
    Join Date
    Jul 2014
    Posts
    294
    Thanks for the reply.
    Can u please tell me what i am missing here? I am going to install this on client machine.

    These are the db specifications on my system

    Number compatibility = ON
    Varchar2 compatibility = ON
    Date compatibility = ON

    db2 => select current date from sysibm.sysdummy1

    1
    -------------------
    2015-02-10-17.40.09

    1 record(s) selected.

    These are the db settings on the client side

    Number compatibility = OFF
    Varchar2 compatibility = OFF
    Date compatibility = OFF

    db2 => select current date from sysibm.sysdummy1

    1
    -------------------
    10/02/2015

    1 record(s) selected.

    I read about date compatibility on db cfg. tried to turn off the date compatibility on my machine, but it is not happening.
    Kindly let me know , what i have to change on my system , before I am going to run this function on client side.We don't have any db2 admin department to handle this I have to look on my own.
    Last edited by HABBIE; 02-10-15 at 08:17.

  6. #6
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    what kind of connection is client using ?
    the client bind files can be bound with specific date/time settings
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  7. #7
    Join Date
    Jul 2014
    Posts
    294
    Client machine: Windows , db2 c express.
    I am working physically on client machine.

  8. #8
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    db2 c express is server not client
    is the db local or remote
    you have to be more specific to avoid all these communications ... and clarifying exactly your setup and environment
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  9. #9
    Join Date
    Jul 2014
    Posts
    294
    Client database is local.
    Server : Db2 c express
    os: windows

  10. #10
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Apply the formatting to the result of the udf after the call.
    Only apply the formatting when displaying to an end user or outputting to external interface.
    You don't need to format when just passing the date object around inside db2.
    For example:
    values varchar_format(li_date(current date),'YYYY/MONTH/DD') --> includes translated locale-specific month name
    values varchar_format(li_date(current date),'YYYY/MM/DD') --> includes month-number 01..12.

  11. #11
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    thanks Mor I was just about to suggest that you set the variable to the date format desired. This way your client your server, your version will no longer matter.
    Dave

Tags for this Thread

Posting Permissions

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