Querying data from quite old IBM iseries AS400 DB2 (date format issue)

0
votes

I am quite a newbie in querying data from databases and now I am currently having an issue with date format in very old (I don't know exact version) IBM iSeries AS400 DB2 database. My problem is that the date is stored in this DB in three separate columns as whole number (column day + column month + column year) and I need to connect to this DB via ODBC in Excel and filter just a few rows according to desired date span (e.g. from 1st December 2019 until 31st December 2019). In this case I don't want to use PowerQuery to do all the modifications, because the complete table has millions of rows. I want to specify the filter criteria within SQL string so the PowerQuery doesn't have to load all the rows...

My approach was following:

I've created 6 parameter cells in Excel sheet where I simply defined Date From (e.g. cell 1 = '01', cell 2 = '12' and cell 3 = '2019') and Date To (same logic for parameter cells 4, 5 and 6). Then I mentioned these parameter cells in SQL string where I defined:

(Day >= Parameter cell 1, Month >= Parameter cell 2, Year >= Parameter cell 3) and (Day <= Parameter cell 4 etc.)

This worked quite good for me, but only when I liked to export just a few hundreds of lines within the same year. But now I am facing to an issue when I like to export data from 1st December 2019 to 31st January 2020. In this case my "logic" doesn't work, because Month From is '12' and Month To is '01'.

I've tried another approach with concat SQL function to create text column like '2019-12-01' and then convert this column to datetime format (with cast to varchar8 first), but it seems that this approach doesn't work for me, because everytime I get an error which says: "Global variable DATETIME not found".

Before I post you some of my code, could I ask you for an advise, if you can think of a better solution or approach for my issue?

Many thanks and have a great day :-)

1
sqlexcelibm-midrangepowerquerydb2-400
DATETIME doesn't exists on IBMi, DATE('2019-12-01') will probably work, are you sure all you dates are correct (no 29 feb on non leap years, no 31 june, etc) – nfgl
what does it return when you run select OS_VERSION, OS_RELEASE from sysibmadm.env_sys_info ? – nfgl
Please post code, then ask for advise. Otherwise any advise we could give is just a shot in the dark. Honestly, the concatenate, or arithmetic method could be your best bet. You may not even have to convert to a date type. We need code first. – jmarkmurphy
nfgl: thank you for your reply and nice command. OS version = 7, OS release = 2 – Jan Klaška
@JanKlaška: V7R2 is not very old. :-) It's from 2014 and still supported by IBM. – PoC

1 Answers

1
votes

A simple solution would be

select * from table where year * 100 + month between 201912 and 202001