Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2005
    Posts
    5

    Unanswered: How to convert current_date to julian(105047)

    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

  2. #2
    Join Date
    Dec 2002
    Posts
    58
    Is this what you're looking for ?

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

  3. #3
    Join Date
    Feb 2005
    Posts
    5
    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.

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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;

  5. #5
    Join Date
    Feb 2005
    Posts
    5
    I will try to view data and let you know if this works.

  6. #6
    Join Date
    Feb 2005
    Posts
    5
    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

  7. #7
    Join Date
    Feb 2005
    Posts
    5
    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.

  8. #8
    Join Date
    Jan 2005
    Posts
    191
    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

Posting Permissions

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