Topic
  • 10 replies
  • Latest Post - ‏2013-05-20T16:00:53Z by NickLawrence
Junius
Junius
21 Posts

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
    281 Posts
    ACCEPTED ANSWER

    Re: Current Date in YYMMDD format

    ‏2013-05-15T07:29:40Z  

    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

  • B.Hauser
    B.Hauser
    281 Posts

    Re: Current Date in YYMMDD format

    ‏2013-05-15T07:29:40Z  

    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
    21 Posts

    Re: Current Date in YYMMDD format

    ‏2013-05-15T17:31:48Z  
    • B.Hauser
    • ‏2013-05-15T07:29:40Z

    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

    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

  • krmilligan
    krmilligan
    450 Posts

    Re: Current Date in YYMMDD format

    ‏2013-05-15T18:07:53Z  

    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.

     

  • B.Hauser
    B.Hauser
    281 Posts

    Re: Current Date in YYMMDD format

    ‏2013-05-16T08:47:49Z  
    • Junius
    • ‏2013-05-15T17:31:48Z

    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

    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
    21 Posts

    Re: Current Date in YYMMDD format

    ‏2013-05-16T21:07:48Z  
    • B.Hauser
    • ‏2013-05-16T08:47:49Z

    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

    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".

  • Junius
    Junius
    21 Posts

    Re: Current Date in YYMMDD format

    ‏2013-05-16T21:20:01Z  

    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.

     

    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
    281 Posts

    Re: Current Date in YYMMDD format

    ‏2013-05-18T16:08:39Z  
    • Junius
    • ‏2013-05-16T21:20:01Z

    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.

    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

  • B.Hauser
    B.Hauser
    281 Posts

    Re: Current Date in YYMMDD format

    ‏2013-05-18T16:16:48Z  

    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.

     

    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
    450 Posts

    Re: Current Date in YYMMDD format

    ‏2013-05-20T15:07:09Z  
    • B.Hauser
    • ‏2013-05-18T16:16:48Z

    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

    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. 

     

     

  • NickLawrence
    NickLawrence
    69 Posts

    Re: Current Date in YYMMDD format

    ‏2013-05-20T16:00:53Z  
    • B.Hauser
    • ‏2013-05-18T16:08:39Z

    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

    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