Create a year, month, day field from a date

[#1]

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';

[oscar_ortiz · Accepted solution]

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

[hic]

Oscar's solution works excellently. However, if you have a very large transaction table, you do not want to add new fields to this table. Then you should instead use the following construction:

TransactionTable:

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

Calendar:

Load distinct

     YourDateField,

     Month(YourDateField) as myMonth,

     Day(YourDateField) as myDay,

     Year(YourDateField) as myYear

     Resident TransactionTable;

[#4]

Henric,  I only have about 27,000 records so Oscars solution worked for me.  However, I believe I may use yours in the future.  I appreciate both of your quick responses!