1

Is there any function to convert Normal Date to Julian Date.

I have used JulianDayFromDate function in transformer but i am not getting expected output .

Sample Input : 

Date -- 2013-02-02

Output Should be:

Julian Date-- 113033

( In Database we can do the query as below )

select to_date(1900000+113033,'YYYYDDD') from dual

But how to convert in Datastage ... ?

avinash
  • 119
  • 1
  • 13

2 Answers2

2

Maybe your expectations are wrong - 2456326 is the julian day for 2013-02-02 - the DataStage functions works.

Check out the Wikipedia documentation for defintions and calculations

Not sure what your 113033 is but it is not the Julian date or Julian day for the date shown.

To achieve what you want you have to do the calculation by your own. Besides the year calcuation you could use YeardayFromDate to get the daynumer in the year. So finally it would be something like 

YearFromDate('2013-02-02') * 1000 - 1900000  +   YeardayFromDate('2013-02-02')
MichaelTiefenbacher
  • 3,805
  • 2
  • 11
  • 17
  • How can we convert again back to normal date. After i apply this conversion my date has been converted to valid Julian Date. Now can we convert this Julian Date back to Normal Date? – avinash Jun 09 '19 at 20:34
0

Databases use different "day zero" values for Julian dates. Doesn't matter. The point is the ability to do date (day) arithmetic with them. To convert back, use DateFromJulianDay() function.

Ray Wurlod
  • 831
  • 1
  • 4
  • 3