ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

SQL/400 Numeric date in DDMMYY format to Character date in YYYYMMDD format

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL/400 Numeric date in DDMMYY format to Character date in YYYYMMDD format

    Dates in our DB is numeric with date format DDMMYY.
    I wrote an SQL statement to select them as strings in YYYYMMDD format.
    The problem lies where I fetch the century part, since the FROM DATE format is only YY.
    For the time being I have hardcoded '20'. But I don't feel good when I do that. How can I convert century part of a numeric date field with format DDMMYY without hardcoding?

    Given below is my SQL statement.

    Field, ODATE is of type 6S 0 and holds date in DDMMYY FORMAT

    Code:
    SELECT
    CASE WHEN LENGTH(TRIM(CHAR(ODATE))) = 5 THEN
    '20' CONCAT RIGHT(TRIM(CHAR(ODATE)),2) CONCAT
    SUBSTR(TRIM(CHAR(ODATE)),2,2) CONCAT
    '0' CONCAT LEFT(TRIM(CHAR(ODATE)),1)
    WHEN LENGTH(TRIM(CHAR(ODATE))) =  6 THEN
    '20' CONCAT RIGHT(TRIM(CHAR(ODATE)),2) CONCAT
    SUBSTR(TRIM(CHAR(ODATE)),3,2) CONCAT
    LEFT(TRIM(CHAR(ODATE)),2)
    END CASE
    FROM MYLIBL/MYFILE

  • #2
    Re: SQL/400 Numeric date in DDMMYY format to Character date in YYYYMMDD format

    Hi,

    1. I always ask myself, how someone can store dates as numeric values in the format DDMMYY. It is not possible to sort the results nothing! Everything must be handled in the programs.
    You application is not the only one. I just recently got a brand new program and DDS described file from our external programmer, who stored the dates in the table in this way.
    When I asked him why he told me its the easiest way to use the RPG TIME-OpCode and move it simply into the database field!

    2. I'd not convert a numeric date into a character date, I'd prefer to convert it into a real date.

    3. If you really need it in this way I'd prefere to write a simple RPG procedure one or two statements and register this RPG procedure as UDF.

    4. If you really need it in this way in SQL - here is a possible solution:
    PHP Code:
    case when mod(ODate100between 0 and 39 Then '20'
         
    else '19' end                                  
    concat substr
    (digits(ODate), 52)                  
    concat substr(digits(ODate), 32)                  
    concat substr(digits(ODate), 12
    Birgitta
    Last edited by kitvb1; August 21, 2008, 06:56 AM.

    Comment


    • #3
      Re: SQL/400 Numeric date in DDMMYY format to Character date in YYYYMMDD format

      Thank you Brigitta.
      I'd rather handle it in RPG program.

      Comment

      Working...
      X