Julian date conversion to yyyy-mm-dd : hh-mm-ss

[via]

Hello,
I made a search for converting julian date to yyyy-mm-dd : hh-mm-ss.
I tryed the following in the transformer stage variables as

var1=Oconv(Iconv(TheDate, "D4YJ"), "D/YMD[2,2,4]")
var2 =Oconv(Iconv(TheTime, "MTS"), "MTS:")

And i tryed concatination in the OUT as

CAT var1 ":" CAT var2

Incomming date is cyynnn
(eg:105118,105139,......)
c =year(1 as 2000 and 0 as 1900)
yy= two digit year
nnn= day in the year

And Incomming time is of 171023,103338,....

I read in the forum for a similar question saying that the basic ICONv and OCONv doent work in the Paralle transformer!
As i write these code in the transormer stage, it remains RED
Could some one suggest me a solution to my problem?
Please help,
Thanks
Via

[ray.wurlod]

This exact question has been answered in the past.

It's true that Oconv() and Iconv() are not available in parallel jobs, unless you use a BASIC Transformer stage.

Include "century" in your search terms.

[via]

Thank you Ray,
But cyynnn is some thing unique that i am unable to resolve. Could you please suggest how to hold the c value in cyynnn format to yyyy-mm-dd using the same iconv and oconv?

Thanks again,
Via

[ameyvaidya]

Hi Via,
That is a Trademark (well almost) JD Edwards date format.
Dont have DSEE so cant give actual code:

Step 1 is to seperate the year from the source:
* for String input take everything from pos 1 to len-3; convert to Integer
* for Integer divide by 1000 and use AsInteger()
Add 1899 to either result.

Step 2 is to generate the year component as a date:
use StringToDate(DecimalToString(<Year from Step1>):"-12-31")
IMPORTANT: Watch out for the leading zeroes spaces or "." that DataStage PX tends to add to Numbers.

Step 3 is to add the days from the Julian Date
* for String input take last 3 characters; convert to Integer
* for Integer mod by 1000 and use AsInteger()

use
DateFromDaysSince(<Integer from Step3>, <date from Step2>);

That should work in PX.
However if you can, I'd recommend either a UDF in SQL on the Database server or a custom C function.

HTH

[clshore]

I believe you must add 1900 to the first result, not 1899:

ex) 105030 = 2005-01-30
Int(105030/1000) = 105
105 + 1900 = 2005

Carter

[via]

Thanks Amey Vaidya and Carter for your input,
I am working on this, let me find out how it turnout.
Thanks again,
Via

[dxp]

Hello,


I read in the forum for a similar question saying that the basic ICONv and OCONv doent work in the Paralle transformer!
As i write these code in the transormer stage, it remains RED
Could some one suggest me a solution to my problem?
Please help,
Thanks
Via

Hi,
hope this link will answer ur question.....

viewtopic.php?t=87162&highlight=

dxp.

[via]

But the date is in cyynnn format
c:century
yy:year
nnn: day number
My initial problem itself is to conver this cyynnn to the ordinary date.
Thanks any way,
Via

[ameyvaidya]

Carter,
The reason I added 1899 and not 1900 is because:

105001 is 01 Jan 2005

Now to use the defined logic:
1899+105=2004.
Create date 31- Dec- 2004
Add 1(001) day to 31 Dec 2004

Which is 1-Jan-2005

If we use 1900, we have to create the date as 1-Jan-(1900+cyy) subtract 1 day from our final addition.

Both are valid.

HTH

[via]

Hello folks,
After 3 days of work with all the iconv,oconv and other procedure, here i found a way to convert my cyynnn and hhmmss sources in to a string. Left side are stage variables in the transformer with datatypes.

(varchar)y2d= TrimLeadingTrailing(IN.Date[3,2])

(integer)y4d= '20':y2d

(varchar)fd= y4d:'-01-01'

(integer)dn= AsInteger(IN.DATE[5,3]-1)

(date)ad=DateFromDaysSince(dn,fd)

(integer)hh=IN.time[2,2]

(integer)mm=IN.time[4,2]

(integer)ss=IN.time[6,2]

(varchar)tt=hh:":":mm:":":ss

(varchar)ts=ad: " " : tt (This is my final string with date and time)

StringToTimestamp(timestamp,"%yyyy-%mm-%dd %hh:%nn:%ss") (this is converted to timestamp)

Every thing is fine and i wrote to a seq file as a timestamp.

Now, the problem is writing this timestamp to SQL server database.
I have created a column with data type as timestamp.
when i imported the metadata for this table the time stamp column is showing as BINARY! . I even forcechanged to timestamp in the destination coulums but with compilation error saying "Invalid conversion requested from a timestamp to a binary"
And so i am unable to push my timestamp data in to this column
Did it happen to any of you guys?
Any input or suggestion is appreciated,
Thanks
Via

[Bala R]

From SQL server 7.0 online books:

Code: Select all

timestamp (T-SQL)
A database-wide unique number. The storage size is 8 bytes.

Remarks
A table can have only one timestamp column. The value in the timestamp column is updated every time a row containing a timestamp column is inserted or updated.The value of a timestamp column is unique within a database.
A nonnullable timestamp column is semantically equivalent to a binary(8) column. A nullable timestamp column is semantically equivalent to a varbinary(8) column.

If your datatype relates to the above then you should not try to insert the column. Its auto generated when you insert each row. IHTH

[via]

Thanks bala,
I too have gone through the sql book...didn't understand
Could You plz eloborate....
With out inserting(mapping) how will it populate to the table?
Regards,
Via

[clshore]

Be careful with your assumption that all dates will be in 21 century.
The code below will fail with an input date of 99032 (1999-02-01).
The lower 3 digits are the day of the year, the remaining upper digits are years since 1900.

Carter

Hello folks,
After 3 days of work with all the iconv,oconv and other procedure, here i found a way to convert my cyynnn and hhmmss sources in to a string. Left side are stage variables in the transformer with datatypes.

(varchar)y2d= TrimLeadingTrailing(IN.Date[3,2])

(integer)y4d= '20':y2d

(varchar)fd= y4d:'-01-01'

(integer)dn= AsInteger(IN.DATE[5,3]-1)

(date)ad=DateFromDaysSince(dn,fd)

(integer)hh=IN.time[2,2]

(integer)mm=IN.time[4,2]

(integer)ss=IN.time[6,2]

(varchar)tt=hh:":":mm:":":ss

(varchar)ts=ad: " " : tt (This is my final string with date and time)

StringToTimestamp(timestamp,"%yyyy-%mm-%dd %hh:%nn:%ss") (this is converted to timestamp)

Every thing is fine and i wrote to a seq file as a timestamp.

Now, the problem is writing this timestamp to SQL server database.
I have created a column with data type as timestamp.
when i imported the metadata for this table the time stamp column is showing as BINARY! . I even forcechanged to timestamp in the destination coulums but with compilation error saying "Invalid conversion requested from a timestamp to a binary"
And so i am unable to push my timestamp data in to this column
Did it happen to any of you guys?
Any input or suggestion is appreciated,
Thanks
Via

[dxp]

But the date is in cyynnn format
c:century
yy:year
nnn: day number
My initial problem itself is to conver this cyynnn to the ordinary date.
Thanks any way,
Via

Hi via,

just replace the data format wherever it is. i think it works. i mean u said that the date is in cyynnn format. may be it may work, if u try to give ur input date in the format.
just a thought.

[ameyvaidya]

Hi Via,
I think the issue now is the base database table.

The data type TimeStamp is (Possibly??) incorrect for what I think You want to achieve.

Refer link:
http://msdn.microsoft.com/library/defau ... b_7msw.asp

Code: Select all

timestamp

A database-wide unique number that gets updated every time a row gets updated.

What you really need is:

Code: Select all

datetime

Date and time data from January 1, 1753, through December 31, 9999, with an accuracy of three-hundredths of a second, or 3.33 milliseconds.

You might want to ask your DBA or Data Architect to update the table DDL.