Help needed to compare dates in DB2

maheshrama · #1 (**permalink**) 10-02-09, 18:52

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

Stealth_DBA · #2 (**permalink**) 10-02-09, 21:28

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).

tonkuma · #3 (**permalink**) 10-02-09, 23:39

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

DB2Plus · #4 (**permalink**) 10-03-09, 00:08

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

Quote:

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

Kara S.