Hello,

I am new to DB2 and this is my first post. I need to see if a date column 'YRGIVING' is equal to the current year 2008. I have this in my query 'and YRGIVING = YEAR(CURRENT DATE)'.

I am getting this error:
SQLCODE = -401, ERROR: THE OPERANDS OF AN ARITHMETIC OR COMPARISON OPERATION ARE NOT COMPARABLE

This will be an automatic process so I can't hardcode '2008' in the query. Can anyone help me please?

Thank you!

Kevin,
Off the top of the head guess, a data type mismatch. Your yrgiving column is possibly defined as CHAR??

Dave

If YRGIVING is defined as a DB2 date column, then it has more than just the year.

Try: 'and YEAR(YRGIVING) = YEAR(CURRENT DATE)'.

If YRGIVING is not a date column, then dav1mo probably has the correct answer.

Thank you both for your help!!

YRGIVING is defined as CHAR. So it is a data type mismatch.
How can I use YRGIVING to compare the date in this case?

Could I use 'cast' to mask YRGIVING as a date type?

I did a quick search on this forum and came up with this :

YRGIVING is defined as char (04);
FYSTART is defined as date;

SELECT CAST(YEAR(B.FYSTART) AS CHAR(04))
FROM TB945 A,
TBDAT B
WHERE A.ENTITYID = B.ENTITYID
AND A.YRGIVING = B.FYSTART

This is the error I got:

SQLCODE = -180, ERROR: THE DATE, TIME, OR TIMESTAMP VALUE YRGIVING IS INVALID

Any suggestions?
Regards,
Kevin

Try:

'and cast (YRGIVING as int) = YEAR(CURRENT DATE)';

Marcus,

That worked beautifully !!!
Thanks!

Kevin

also, just so you know, you casted what you were selecting as char, but in your where clause you were comparing a char 4 column to a date column, therefore the -180. If you were to put the cast function in the where clause when you comapre the 2 columns it will work as did Marcus' solution with casting the yrgiving as an int. Keep in mind issues with indexing and if you will be able to match columns as these tables grow.

Dave

Dave,

Thanks for the input. The more I play with this the more it makes sense. You guys are great. Thanks again!

Kevin

Another ideas(convert or use expression only for one column):
1) AND A.YRGIVING = SUBSTR(CHAR(B.FYSTART), 1, 4)
2) AND B.FYSTART BETWEEN DATE(A.YRGIVING||'-01-01') AND DATE(A.YRGIVING||'-12-31')