Julian date conversion to yyyy-mm-dd : hh-mm-ss
Moderators: chulett, rschirm, roy
Julian date conversion to yyyy-mm-dd : hh-mm-ss
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
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
Via
-------------------------------------
Learn all the way, as you Grow each Second
-------------------------------------
Learn all the way, as you Grow each Second
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Charter Member
- Posts: 166
- Joined: Wed Mar 16, 2005 6:52 am
- Location: Mumbai, India
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
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
Amey Vaidya<i>
I am rarely happier than when spending an entire day programming my computer to perform automatically a task that it would otherwise take me a good ten seconds to do by hand.</i>
<i>- Douglas Adams</i>
I am rarely happier than when spending an entire day programming my computer to perform automatically a task that it would otherwise take me a good ten seconds to do by hand.</i>
<i>- Douglas Adams</i>
Re: Julian date conversion to yyyy-mm-dd : hh-mm-ss
Hi,via wrote: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
hope this link will answer ur question.....
viewtopic.php?t=87162&highlight=
dxp.
-
- Charter Member
- Posts: 166
- Joined: Wed Mar 16, 2005 6:52 am
- Location: Mumbai, India
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
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
Amey Vaidya<i>
I am rarely happier than when spending an entire day programming my computer to perform automatically a task that it would otherwise take me a good ten seconds to do by hand.</i>
<i>- Douglas Adams</i>
I am rarely happier than when spending an entire day programming my computer to perform automatically a task that it would otherwise take me a good ten seconds to do by hand.</i>
<i>- Douglas Adams</i>
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
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
Via
-------------------------------------
Learn all the way, as you Grow each Second
-------------------------------------
Learn all the way, as you Grow each Second
From SQL server 7.0 online books:
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
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.
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
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
via wrote: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
Hi via,via wrote: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
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.
-
- Charter Member
- Posts: 166
- Joined: Wed Mar 16, 2005 6:52 am
- Location: Mumbai, India
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
What you really need is:
You might want to ask your DBA or Data Architect to update the table DDL.
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.
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.
Amey Vaidya<i>
I am rarely happier than when spending an entire day programming my computer to perform automatically a task that it would otherwise take me a good ten seconds to do by hand.</i>
<i>- Douglas Adams</i>
I am rarely happier than when spending an entire day programming my computer to perform automatically a task that it would otherwise take me a good ten seconds to do by hand.</i>
<i>- Douglas Adams</i>