3 Replies Latest reply: May 25, 2012 10:01 AM by Tammy Danner RSS

    Create a year, month, day field from a date

    Tammy Danner
      I have data coming from DB2 iseries via SQL statement.  How can I pull the year, month, and day from a single date field to use for selection?
      Here's my script:
      ODBC CONNECT TO iSeries (XUserId is xxx, XPassword is xxx);
      SQL SELECT * FROM S106BB2C.REJECT.REJECTNEW where RTYRJ<>'X' and RSTATS <> 'X';
        • Re: Create a year, month, day field from a date
          Oscar Ortiz

          Tammy,

           

          Try adding a preceding load statement to your SQL statement.  In this load statement you can use the vaious QlikView functions to transform your data.

           

          So you statment might look something like this:

           

           

          ODBC CONNECT TO iSeries (XUserId is xxx, XPassword is xxx);

           

          Load

               *,

               Month(YourDateField) as myMonth,

               Day(YourDateField) as myDay,

               Year(YourDateField) as myYear

          ;


          SQL SELECT * FROM S106BB2C.REJECT.REJECTNEW where RTYRJ<>'X' and RSTATS <> 'X';

           

          This will work if your field is truly a date field.  I have seen date fields in DB2 stored as numeric values so you'll need to be aware of that.  If that's the case you'll want to use different functions to extract your dates.

           

          Good Luck

          Oscar