Convert number of days to date

[oracle]

Hi Every one,

I have date format as YYYYDDD.Here YYYY is the year and DDD is the number of days.Here the business requirement is to get the date(YYYY-MM-DD) for perticular day .

fOR Exmaple :

Input =2011090 (2011 is the year and 090 is number of days)

Output=2011-03-31.

Can please help me with this ?

Regards

[TPons]

Hi

In a transformer, use the below expressions in stage variables

svStartOfTheYear(Varchar(10)) = InputDate[1,4]:"-01-01"

svNoOfDaysFromTheYear(Integer(3)) = InputDate[5,3]-1

And apply the following derivation in your output column.

OutputDate = DateFromDaysSince(svNoOfDaysFromTheYear, svStartOfTheYear)

Sample Ouput:

InputDate OutputDate
2011059 2011-02-28
2011090 2011-03-31
2011365 2011-12-31
2010200 2010-07-19
2011000 2010-12-31

Depending upon your extact requirement, you can either use or avoid -1 in the second stage variable's derivation

Thanks
Pons

[suse_dk]

A wise man gave the answer in this post...

viewtopic.php?t=142845

[oracle]

In a transformer, use the below expressions in stage variables
<snip>

Hi,

Its working fine .Thank you very much for ur quick reply

Regards
oraci

[chulett]

The link in Susanne's post shows a much simpler (and technically "more proper") solution.