Modify date_from_julian_date

mjgmc · Post by **mjgmc** » Thu Jul 22, 2010 2:45 am

Hi,

I'm trying to convert a uint32 field into a date using a Modify Stage and the fonction date_from_julian_day, but I have no chance: All of my records get a NULL at the output field.

I use :

Outfield=date_from_julian_day(Infield)

At the Infield I have this as data:
55398
55399
55400
55401
55402
...

Can anyone help me?

Thanks

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu Jul 22, 2010 4:04 am

Can you try it in a transformer to get it working. Modify stage uses almost similar arguments but reverse function name.

ray.wurlod · Post by **ray.wurlod** » Thu Jul 22, 2010 4:29 am

What are you supposed to get from these inputs? On what base date are your allegedly Julian dates based? On what base date is the Julian day used by DataStage based (it's in the manual)? I suspect yours is different.

mjgmc · Post by **mjgmc** » Thu Jul 22, 2010 5:37 am

Sainath.Srinivasan wrote:Can you try it in a transformer to get it working. Modify stage uses almost similar arguments but reverse function name. ...

In a transformer, putting in the derivation:
DateFromJulianDay(L_In.Infield)

I get this warning:
"Conversion error calling conversion routine date_from_julian_day data may have been lost"

mjgmc · Post by **mjgmc** » Thu Jul 22, 2010 5:45 am

ray.wurlod wrote:What are you supposed to get from these inputs? On what base date are your allegedly Julian dates based? On what base date is the Julian day used by DataStage based (it's in the manual)? I suspect ...

I can't read whole your post, but I don't get your point. Why is it important to know where the data is coming from?

ArndW · Post by **ArndW** » Thu Jul 22, 2010 5:56 am

Because the "Julian Date" is not standardized and you posted "55398" as a Julian date but haven't specified what date that is supposed to represent. DataStage PX uses the definition

Parallel Job Developers Guide, p. 62 wrote:A Julian day specifies the date as the number of days from 4713 BCE January 1, 12:00 GMT

mjgmc · Post by **mjgmc** » Thu Jul 22, 2010 6:07 am

ArndW wrote:Because the "Julian Date" is not standardized and you posted "55398" as a Julian date but haven't specified what date that is supposed to represent. DataStage PX uses the definition
[quote="Parallel ...

I supposed Julian Date came from a Calendar for which 0 (or 1, never sure) represents 1960-01-01.

How am I suppose to specify what date that represents?

chulett · Post by **chulett** » Thu Jul 22, 2010 7:38 am

You suppose? You need to find out what those dates translate to before anyone takes more time to guess at the proper solution to automate that.

ArndW · Post by **ArndW** » Thu Jul 22, 2010 7:39 am

Which would mean you would need to apply an offset to your data date in order to get a DataStage Julian date and then you can use the conversion function.

chulett · Post by **chulett** » Thu Jul 22, 2010 7:59 am

Hey Arnd, what is the zero date for julians in Universe? I seem to remember it is old whats-his-name's birthday but can't for the life of me find it. The OP would need to verify their zero date and then offset (as you mentioned) from the internal zero date for this to work.

mjgmc · Post by **mjgmc** » Thu Jul 22, 2010 8:39 am

chulett wrote:You suppose? You need to find out what those dates translate to before anyone takes more time to guess at the proper solution to automate that. ...

Who do you think you are???

A forum is made to provide help, not to provide a way to try to shame people.

I'm trying to figure out how a Datastage fonction works, if you cannot help me go to another post and leave me alone.

mjgmc · Post by **mjgmc** » Thu Jul 22, 2010 9:17 am

I found the answer I was looking for.

Thank you all

chulett · Post by **chulett** » Thu Jul 22, 2010 9:39 am

Shame? Hardly. I apologize if it came off that way to you, it was more of an attempt to get you to think about what you are posting, to provide enough information so that people can actually help you in a cogent manner rather than have to ask for the details needed to provide that help.

It would be appreciated if you could post your actual resolution to help out future searchers... did you indeed need to offset your zero date to match the one DataStage uses internally?

ray.wurlod · Post by **ray.wurlod** » Thu Jul 22, 2010 6:51 pm

UniVerse - and therefore DataStage server jobs - use a base date (day 0) of 1967-12-31.

mjgmc · Post by **mjgmc** » Fri Jul 23, 2010 1:46 am

ray.wurlod wrote:UniVerse - and therefore DataStage server jobs - use a base date (day 0) of 1967-12-31. ...

Are you sure? When you create a date column with a Column Generator in PX (remember you are at the EE forum) and you don't specify any date, by default Datastage gives you 1960-01-01.

But that doesn't correspond to a julian day 0, actually it corresponds to the julian day 2436935.