I need a simple query that would return a julian date for the current_date.
For eg. if the current_date is '2005-02-16' the output should be 105047.

Thanks.
Reg
sun

Is this what you're looking for ?

select julian_day(current timestamp) from sysibm.sysdummy1 ??

No bcos that would julian_day( current_date) would return a 7 digit number....
I need the output to be in the format 105047 ( where 1 is century, 05 is year and 047 is day of the year( jan 31+ feb 16 days)).
Please execute the query on DB2 and then let me know.
I have tried with the following query
SELECT CONCAT(CONCAT ('1',SUBSTR(CHAR(YEAR(CURRENT_DATE)),3,2)),SUBSTR( CHAR(DIGITS(DAYOFYEAR(CURRENT_DATE))),8,3)) FROM SYSIBM.SYSDUMMY1;

This would return 107653 but is not working when I substitue in the
user defined sql query in ODBC stage after where clause

like select * from table xyz where somedb2_date = the above query.

HELP.

I don't understand why "century" would be "1" for the year 2005 ...
Anyhow, what about the following:

SELECT 1 * 100000 + (YEAR(CURRENT_DATE)-2000) * 1000 + DAYOFYEAR(CURRENT_DATE) FROM SYSIBM.SYSDUMMY1;

I will try to view data and let you know if this works.

I tried by putting the same query in the where cluase and tried to view data.
I got a error regarding '1' that in the starting of the query.
error : Illegal symbol 1.

SELECT 1 * 100000 + (YEAR(CURRENT_DATE)-2000) * 1000 + DAYOFYEAR(CURRENT_DATE) FROM SYSIBM.SYSDUMMY1

I tried by putting the same query in the where cluase and tried to view data.
I got a error regarding '1' that in the starting of the query.
error : Illegal symbol 1.

SELECT 1 * 100000 + (YEAR(CURRENT_DATE)-2000) * 1000 + DAYOFYEAR(CURRENT_DATE) FROM SYSIBM.SYSDUMMY1

the left hand side of my query is a Numeric field.

Most people, me included, find it VERY difficult to solve syntax problems when they don't know the syntax that created the problem. You must be one of the rare people who can - because you expect the rest of us to be able to.

James Campbell