IBM Mainframe Forum

Mainframe Technical Support Forums

Skip to content


DATE from day of the year



IBM's flagship relational database management system
Post a reply

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.
Papya013
 
Posts: 35
Joined: Wed Nov 06, 2013 6:53 pm
Has thanked: 0 time
Been thanked: 0 time
Top

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.
Regards
Nic
NicC
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
Top

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.
Raja190
 
Posts: 36
Joined: Mon Dec 14, 2015 8:13 pm
Has thanked: 0 time
Been thanked: 0 time
Top

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
cheers
enrico
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
enrico-sorichetti
Global moderator
 
Posts: 3003
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 164 times
Top

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
Akatsukami
Global moderator
 
Posts: 1058
Joined: Sat Oct 16, 2010 2:31 am
Location: Bloomington, IL
Has thanked: 6 times
Been thanked: 51 times
Top
Post a reply

Return to DB2

 


  • Related topics
    Replies
    Views
    Last post
Forum Rules|FAQ|Subscriptions|Bookmarks