Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2009
    Posts
    2

    Question Unanswered: Help needed to compare dates in DB2

    Hello,

    I would like to compare a numeric field that holds dates defined as DECIMAL (7,0) in YYYYDDD format with CURRENT DATE in YYYY-MM-DD format. I have tried a few SQL's with no luck.

    eg. if the numeric field holds a value of 2009275 (Oct 2, 2009) I would like a SQL that compares 2009275 with CURRENT DATE (2009-10-02).

    Any help will be greatly appreciated?

    Thanks.
    Mahesh

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Mahesh, It would help if you included the DB2 version and operating system. Some functions may not be available in certain versions.

    Your problem (as I am sure you know) is you aren't really comparing Date datatypes. You are comparing a Date datatype with an Decimal datatype. You need to convert the information into comparable types. Here is one way (I haven't tested but it should work):

    ( YEAR(CURRENT_DATE) * 1000 ) + DAYOFYEAR(CURRENT_DATE)

    This will extract out the Year part (2009) as an Integer. Multiplied by 1000 shifts the value to the right 'value' (2009000). DAYOFYEAR extracts out the Julian day of the year (275 for 2009-10-02 I think) as an Integer. Adding this to 2009000 would result in an Integer value of 2009275.

    This should be comparable to a decimal(7,0) value. If not, you can use CAST to change it to a Decimal(7,0) value.

    CAST( YEAR(CURRENT_DATE) * 1000 ) + DAYOFYEAR(CURRENT_DATE) AS DECIMAL(7,0) )

    There are other ways to accomplish this. You could extract the values from the DECIMAL(7,0) column, convert them to Character strings and use one of the Formatting functions (like TO_DATE).

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I agree with Stealth DBA.

    Another way would be to convert DECIMAL (7,0) in YYYYDDD format to DATE datatype. Like this:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT dec_date
         , DATE(DIGITS(dec_date)) date
      FROM (SELECT 2009275. FROM sysibm.sysdummy1) AS d(dec_date);
    ------------------------------------------------------------------------------
    
    DEC_DATE  DATE      
    --------- ----------
     2009275. 2009-10-02
    
      1 record(s) selected.
    DATE('yyyynnn') is supported on DB2 UDB for: z/OS V8, iSeries V5R3, LUW V8.

    Please see "SQL Reference for Cross-Platform Development Version 2 (DB2 UDB for: z/OS V8, iSeries V5R3, LUW V8)" in The SQL Reference for Cross-Platform Development

  4. #4
    Join Date
    Jul 2009
    Posts
    150
    Example 2:
    Assume that DATCOL is a CHAR(7) column in some table, and that one of its values is the character string '1989061'. For this value, the following statement returns the internal representation of 2 March 1989.

    DATE(DATCOL)

    3.2.26 "DB2 V9.1 for z/OS SQL Reference" IBM Library Server

    So, you have to

    select date(char(decdate, 7)), CURRENT DATE
    FROM your_table

    Kara S.
    Last edited by DB2Plus; 10-03-09 at 19:28.

  5. #5
    Join Date
    Oct 2009
    Posts
    2
    This is great information. I got it working. Thanks a bunch guys...You all are awesome....Mahesh

Posting Permissions

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