Results 1 to 10 of 10

Thread: Compare Date

  1. #1
    Join Date
    Aug 2008
    Posts
    55

    Unanswered: Compare Date

    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!

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Kevin,
    Off the top of the head guess, a data type mismatch. Your yrgiving column is possibly defined as CHAR??

    Dave

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Aug 2008
    Posts
    55
    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?
    Last edited by KevinYC; 08-29-08 at 10:29.

  5. #5
    Join Date
    Aug 2008
    Posts
    55
    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
    Last edited by KevinYC; 08-29-08 at 10:34.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Try:

    'and cast (YRGIVING as int) = YEAR(CURRENT DATE)';
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Aug 2008
    Posts
    55
    Marcus,

    That worked beautifully !!!
    Thanks!

    Kevin

  8. #8
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

  9. #9
    Join Date
    Aug 2008
    Posts
    55
    Dave,

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

    Kevin

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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
    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')

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •