How to retrieve a DB2 date in Julian format

How do I get the DB2 date from a table in Julian format? I did write a query concatenating year and days, but it wasn't useful. It gave the SQL error -171. Please help.

SQL error -171 means "The SQL statement includes an unknown scalar function." Without seeing your query, I cannot tell what you did wrong. But never mind. I think the following will do it:

select year(thedate)
     , dayofyear(thedate)
  from yourtable

YEAR() and DAYOFYEAR() are DB2 scalar functions which return integers for the year and day of year (1-366), respectively. The day of year between 1 and 366 is what most people mean when they use the term "Julian."

If, instead of integers, you wanted a YYYYDDD string, you could use the CAST function:

select cast( year(thedate) * 1000
             + dayofyear(thedate) as char(7) )
  from yourtable

For More Information

• Dozens more answers to tough SQL questions from Rudy Limeback.
• The Best SQL Web Links: tips, tutorials, scripts, and more.
• Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.