Convert current_date(DB2) into Julian format( In ODBC Stage)

[sun786]

In a ODBC stage I have a user defined sql query with DB2 as database.
The sql query looks like this
select * from table_xyz where some_date = current_date
some_date is in Julian format.
I want the current_date also to be in julian format(105047).
current_date is like sysdate in oracle.
Some one help me with a function or some query which returns a julian date for the current_date.
Note: No oconv or iconv as this is in the ODBC stage.
DS Gurus.....pls dont ask me to go to DB2 forum.
Thanks
sun.

[roy]

Hi,
DS Gurus can answer DS questions, some are experianced enough to have learned a specific flavour of DB to know it's conversion functions.
we can offer functions to be implemented in a transformer stage but in the SQL is a pure DB matter not DS.
try looking in the help and search for julian date, it will pop up several routines like: JulianDayFromDate & DateFromJulianDay
maybe it will help.

(hmmm my 777 post answering the 77th post of another interesting ...)
IHTH,

[Sainath.Srinivasan]

Try something like
SELECT Int(YourDate - '12/31/1967') From YourTableName
in your SQL

Check your date format - mine was mm/dd/yyyy.

[ray.wurlod]

Or CAST your some_date column as DATE so that its type corresponds with the type of the constant CURRENT_DATE.

[vaby]

Hi Sun,

The following DB2 SQL may help:

select
current_date "DB2 current_date",
decimal((year(current_date)-1900)*1000+dayofyear(current_date),6) "Julian Date"
from
otgdb2.dual

It returns (in my environment):

DB2 current_date Julian Date
17-02-2005 105048

This SQL statement does not provide Null or zero exception handling, so to be on a safe side you can create a custom SQL function on DB2 side similar to:

BEGIN
DECLARE F_OUTPUT DECIMAL ( 6 , 0 ) ;
DECLARE F_TEST INTEGER ;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET F_TEST = 1 ;
SET F_TEST = 0 ;
IF GREGDATE <= DATE ( '1899-12-31' ) THEN RETURN NULL ;
END IF ;
SET F_OUTPUT = DECIMAL ( ( YEAR ( GREGDATE ) - 1900 ) * 1000 + DAYOFYEAR ( GREGDATE ) , 6 ) ;
IF F_TEST = 0 THEN RETURN F_OUTPUT ;
ELSE RETURN NULL ;
END IF ;
END

Usage in your SQL would be (say you save this function as getjul in QGPL):

select
current_date "DB2 current_date",
decimal((year(current_date)-1900)*1000+dayofyear(current_date),6) "Julian Date",
qgpl.getjul(current_date) "Custom DB2 Function"
from
otgdb2.dual

DB2 current_date Julian Date Custom DB2 Function
17-02-2005 105048 105048

HTH,

Vladimir