Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
10 replies Latest Post - ‏2013-05-20T16:00:53Z by NickLawrence
Junius
Junius
17 Posts
ACCEPTED ANSWER

Pinned topic Current Date in YYMMDD format

‏2013-05-14T17:10:29Z |

I need the current date in YYMMDD format in a User Defined Function (UDF).  I tried the following statements, but I get a CAST error.

DECLARE @TODAY DECIMAL(6,0);

SET @TODAY = DEC( substr(char(CURDATE()),3,2)||

                                   substr(char(CURDATE()),6,2)||

                                   substr(char(CURDATE()),9,2) ,6,0) ;

Although, if I run the following statement interactively, it works !  It returns todays date in YYMMDD format as I would expect.

select  DEC( substr(char(CURDATE()),3,2)||
                        substr(char(CURDATE()),6,2)||
                        substr(char(CURDATE()),9,2)  ,6,0 )             
     from sysibm/sysdummy1;

Why do I get a casting error when it's used in a SET statement???

Is there an easier way to get the current date in YYMMDD format?

 

 

 

 

 

 

Updated on 2013-05-14T17:12:09Z at 2013-05-14T17:12:09Z by Junius
  • B.Hauser
    B.Hauser
    250 Posts
    ACCEPTED ANSWER

    Re: Current Date in YYMMDD format

    ‏2013-05-15T07:29:40Z  in response to Junius

    If you are on release 6.1 or higher, you can use the VARCHAR_FORMAT scalar function and cast the result into 8, 0. If you are not yet on release 6.1 you need to convert the current date into a character representation in ISO format, remove the dash separators and the century by using the substring function and finally convert the result into 8, 0.

    Select Cast(VarChar_Format(Current_Timestamp, 'YYMMDD') as Dec(8, 0)),

           Cast(Substr(Replace(Char(Current_Date, ISO), '-', ''), 3) as Dec(8, 0))

    From SysIbm/sysdummy1
     

    Birgitta

    • Junius
      Junius
      17 Posts
      ACCEPTED ANSWER

      Re: Current Date in YYMMDD format

      ‏2013-05-15T17:31:48Z  in response to B.Hauser

      Thank you Birgitta, I will use one of you examples.

       

      I'm still curious about what is wrong with my SET statement.  Why does it generate a CAST error?

       

      Thanks,

      Jay

      • B.Hauser
        B.Hauser
        250 Posts
        ACCEPTED ANSWER

        Re: Current Date in YYMMDD format

        ‏2013-05-16T08:47:49Z  in response to Junius

        Are you working with an american/english operating system?

        If not ...

        the double pipe (||)  is not international and cannot be used within all environments.

        Are you getting the same error message when replacing the double pipes with simple CONCAT (s)?

        Birgitta

        • Junius
          Junius
          17 Posts
          ACCEPTED ANSWER

          Re: Current Date in YYMMDD format

          ‏2013-05-16T21:07:48Z  in response to B.Hauser

          Yes, I'm using the American/English version.  And as I mentioned in my original post, the expression works as part of a SELELCT statement, but not in the SET statement.

          As Kent mentioned, maybe it's a "defect".

  • krmilligan
    krmilligan
    447 Posts
    ACCEPTED ANSWER

    Re: Current Date in YYMMDD format

    ‏2013-05-15T18:07:53Z  in response to Junius

    Jay,

    Your SET statement works fine on my 7.1 system.  Most likely you're hitting a defect, you could try loading latest Database Group PTF and see if the problem goes away. 

    If you're on 7.1, that might be a good use for a global variable so that value is not being recalculated all of the time.

     

    • Junius
      Junius
      17 Posts
      ACCEPTED ANSWER

      Re: Current Date in YYMMDD format

      ‏2013-05-16T21:20:01Z  in response to krmilligan

      Hi Kent,

      We're on the latest PTFs for 7.1 as of a month ago (beginning of April).

      Regarding the global variables, I've not used that capability yet.  I will look into that.

      Thanks.

      • B.Hauser
        B.Hauser
        250 Posts
        ACCEPTED ANSWER

        Re: Current Date in YYMMDD format

        ‏2013-05-18T16:08:39Z  in response to Junius

        Hi Junius,

        I just created the following procedure and run it successfully on a system with Release 7.1 and the latest PTFs:

        CREATE FUNCTION HSCOMMON05/TODAYNUM()
               RETURNS  DECIMAL (6, 0)  
               LANGUAGE SQL
               MODIFIES SQL DATA
               CONCURRENT ACCESS RESOLUTION DEFAULT
               FENCED  
               DETERMINISTIC
               CALLED ON NULL INPUT
               EXTERNAL ACTION
        Begin
           Declare Today Dec(6, 0);
           set  Today = Dec(Substr(Char(CurDate()), 3, 2) concat
                            Substr(Char(CurDate()), 6, 2) concat
                            Substr(Char(CurDate()), 9, 2), 6, 0);
        Return Today;
        End;

         

        Values(TodayNum());      -- Statement ran successfully

        SELECT TodayNum() from SysIbm/SysDummy1;      -- Statement ran successfully

        Birgitta

        • NickLawrence
          NickLawrence
          58 Posts
          ACCEPTED ANSWER

          Re: Current Date in YYMMDD format

          ‏2013-05-20T16:00:53Z  in response to B.Hauser

          Might be worth mentioning that the DATFMT option can play a role in these types of procedures. 

          I know that there can be issues with character versions of dates and times in procedures or udfs if the format for the value is not specified in the procedure/function and the function/procedure is created on two machines that use different settings.

           

          http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/index.jsp?topic=%2Fdb2%2Frbafzsoption.htm

          Updated on 2013-05-20T16:02:07Z at 2013-05-20T16:02:07Z by NickLawrence
    • B.Hauser
      B.Hauser
      250 Posts
      ACCEPTED ANSWER

      Re: Current Date in YYMMDD format

      ‏2013-05-18T16:16:48Z  in response to krmilligan

      Kent,

      isn't an UDF (without parameters) that is generated with deterministic also executed only once?

      Is there any advantage in using a global variable instead of an UDF for this kind of function?

      I was always under the impression a global variable is implemented in the same way as a deterministic UDF.

      Birgitta

      • krmilligan
        krmilligan
        447 Posts
        ACCEPTED ANSWER

        Re: Current Date in YYMMDD format

        ‏2013-05-20T15:07:09Z  in response to B.Hauser

        A Deterministic UDF may only be run once, but that's up to the optimizer to decide.  I didn't catch that the code was within a UDF.  You're going to cancel out some of the potential performance savings of a Deterministic UDF by creating the UDF as FENCED instead of NOT FENCED. 

        A Global Variable would only get instantiated once with a job.