mainframe db2 – get current month and year

5 pts.
Tags:
DB2
DB2 query
Please advise. My DB2 statement does not work. gl_date is a date field, format is 'yyyy-mm-dd'. select * from db2 table where year(gl_date) = year(current date) and month(gl_date) = month(current date);

Software/Hardware used:
DB2 mainframe in Informatica
ASKED: September 20, 2009  1:24 AM
UPDATED: September 21, 2009  1:41 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Steve

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following