CODEWITHSUNDEEP
ibm-midrangedb2-400
macinjosh1
macinjosh1

Reputation: 11

SQL query help for date

I'm not the best at SQL, so I need some help. I have an existing query we use to pull data from DB2 for IBM i (AS400), and I want to modify it so it just pulls the last 6 months of data rather than constantly changing the range. I believe the OHSTDT field is saved on the server as YYYYMMDD.

WHERE GRPORDH.ODSTDT = GRPPHDR.OHSTDT AND GRPORDH.ODGRPH = GRPPHDR.OHGRPH AND (GRPPHDR.OHSTDT Between {d '2013-01-01'} And {d '2015-06-01'})

Thanks for your help!

Upvotes: 1

Views: 93

Answers (1)

Charles
Charles

Reputation: 23783

Build or download a function that converts Date types to Numeric.

iDate is the one I use:
www.think400.dk/downloads.htm

Then your query becomes:

WHERE GRPORDH.ODSTDT = GRPPHDR.OHSTDT 
  AND GRPORDH.ODGRPH = GRPPHDR.OHGRPH 
  AND GRPPHDR.OHSTDT between ConvertToIDate(CURRENT_DATE - 6 MONTHS,'*CCYMD')
                         AND ConvertToIDate(CURRENT_DATE,'*CCYMD')

Note that the alternate form:

WHERE GRPORDH.ODSTDT = GRPPHDR.OHSTDT 
  AND GRPORDH.ODGRPH = GRPPHDR.OHGRPH 
  AND iDate(GRPPHDR.OHSTDT) between CURRENT_DATE - 6 MONTHS
                         AND CURRENT_DATE

which uses the iDate() function to convert from a numeric date to a date date is not the prefered method. It will work, and you'll find lots of examples that do this. But it performs poorly for two reasons.

  • more conversions
  • indexes over OHSTDT can not be used.

Both the iDate() function and the ConvertToIDate() function are available in the iDate library you can download at the link above.

Upvotes: 1

Related Questions