DATE from day of the year

IBM's flagship relational database management system

DATE from day of the year

Postby Papya013 » Wed Apr 06, 2016 4:11 pm

Hi Team,

I am new to DB2, could you please help me how to get the DATE from day of the year.

Ex: If day of the year if 97 for 2016-04-06

97 will be my input and the expected date is 2016-04-06.
Posts: 35
Joined: Wed Nov 06, 2013 6:53 pm
Has thanked: 0 time
Been thanked: 0 time

Re: DATE from day of the year

Postby NicC » Wed Apr 06, 2016 4:31 pm

Have you looked into the DB2 manual to see if there is a function to do it? If there isn't then you will have to do it in a program. You really should use SQL for what it was designed for - storing and retrieving data not doing your program's work.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Global moderator
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: DATE from day of the year

Postby Raja190 » Sun May 08, 2016 5:59 pm

Hey Papya013,

Can you try with below and let me know the result ?

select (TO_DATE(TO_CHAR(GLDGJ+1900000),'YYYYDDD')) as date from table.

here "GLDGJ " is the field name from table.
Posts: 36
Joined: Mon Dec 14, 2015 8:13 pm
Has thanked: 0 time
Been thanked: 0 time

Re: DATE from day of the year

Postby enrico-sorichetti » Sun May 08, 2016 7:39 pm

the day number as input is not enough,
You need also the year

the day number for march first of a NON leap Year will the same as the day number of february 29th of a leap year
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
Global moderator
Posts: 2999
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 164 times

Re: DATE from day of the year

Postby Akatsukami » Mon May 09, 2016 12:15 am

If the SQL is embedded in another language, would it not be easier to invoke the LE CEEDAYS and CEEDATE functions?
"You have sat too long for any good you have been doing lately ... Depart, I say; and let us have done with you. In the name of God, go!" -- what I say to a junior programmer at least once a day
User avatar
Global moderator
Posts: 1058
Joined: Sat Oct 16, 2010 2:31 am
Location: Bloomington, IL
Has thanked: 6 times
Been thanked: 51 times

Return to DB2


  • Related topics
    Last post