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.