Q
Manage Learn to apply best practices and optimize your operations.

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


This was last published in January 2003

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close