Mainframe DB2 – two of my most favorite words. I love them both
First let me clarify something, ‘gl_date’ is a defined to DB2 as a column with data type DATE. (columns like this have no inherent format. DATES are stored internally inside DB2 as “packed, unsigned” and are only formatted when you retrieve them.)
You did not specify what is the result of your query. Is it getting an error? what is the SQLCODE? is it returning wrong rows?
Your query appears to be syntactically correct. There WHERE clause looks like it should work. Are you sure you have data that matches the where clause; that is, rows that have today’s month & year in them? Indeed, I even tried it using one of my test tables and it worked fine. It is possible that I
You might take a look at your data. Pick out a couple of specific rows. And use that date in your WHERE clause to ensure you are hitting proper table, correct location, and so on. Once you are satisfied with those results, then move on to using CURRENT DATE. Next, add a row to your test table with today’s date in ‘gl_date’ and run your query. It should return the newly added row.
I would also point out that SELECT * is probably not necessary. Rarely does one need every column from a table. Specifying * in your SELECT clause can be expensive. You should specify exactly what columns you want.
And keep in mind that when you use a function in a WHERE clause, even if you have an index on the date column in question, DB2 cannot use the index directly… unless … (but I won’t get into this right now.) The point is to try to avoid functions in a WHERE clause.