Date Conversion

[samu]

My source data is a flat file in which i am getting date column as 'CYYMMDD' where C = 1 or 0 which represents 2000 and 1900 centuries, YY - year, MM - month and DD - date.

For example: source column is in the format '1050713', target should be in this format ' July 13, 2005'. Any advice of how to do this in PX. I would really appreciate that.

[kcbland]

Since leading zeroes need to be retained, you should be reading this column as char and conditionally derive the date value by either adding 1900 or 2000 to the YYMMDD portion and then casting into the appropriate target column.

[samu]

thanks, it worked

[clshore]

I think you will find that the year portion can be expressed as the number of years since 1900.

Thus, 2005 becomes 105, 1997 becomes 097, etc.

Knowing this may help you with any future date arithmetic.

Carter

[ravij]

thanks, it worked

Hi Samu could u give the function u used to get the date format n how did u eliminate the 1 from the date given in flat file

thanks in advance.
RK

[samu]

thanks, it worked

Hi Samu could u give the function u used to get the date format n how did u eliminate the 1 from the date given in flat file

thanks in advance.
RK

Hi ravij,

I didn't use any function to get my date format., simply i implemented exactly what kcdland suggested ie., simply add 19 for 0 and 20 for 1 and take it as char. U can eliminate 1 or 0 by using a little bit of logic and then concatinate (add 19 and 20) the string to get ur YYYYMMDD date format and then convert string to date. Hope this will clear somethings for u.

samu

[ray.wurlod]

But someone else on the early part of the learning curve might like to benefit from your experience. Can you advise what stage type you used and what expression(s) you used to solve this?

[samu]

But someone else on the early part of the learning curve might like to benefit from your experience. Can you advise what stage type you used and what expression(s) you used to solve this?

Hi Ray,

Let me explain it clearly. Iam getting date format from flat file which is in the form of CYYMMDD ., in which C-century, YY-year, MM-month and DD-date.

For example: in source if i have '1050414' , i need to get it in form 'April 14,2005'

So they are 2 ways to go about this, if you have data type of the DATE in the source as 'date' and not 'varchar or char' then you have to convert it into 'string' by using transformer stage, by defining a stage variable or in the derivation cell., simply convert ur date column into string by going into 'Functions' and then in 'function' go to 'type conversion' and in 'type conversion' goto 'datetostring(%inputcolumn%)' where in you can give which column needs to be converted to string.
(P.S: you don't need this if ur data type is defined as 'varchar or char')

After that define another stage variable in the transformer just below the first stage variable
(P.S : don't create it above the first stage variable, it wouldn't work since Datastage follows a order in solving stage variables).

In that use 'if then else logic' where in you use 'if (input column[1,1] = '1' then '20':inputcolumn[2,6] else '19':inputcolumn[2,6]'
After that once again create another stage variable below stage variable 2 , where in you would be converting this string to date in the same way as u did for first stage variable but here u have to use 'stringtodate(%inputcolumn%)' and then put this stage variable in your target column in transformer stage whose datatype must be defined as date. Then while loading in to respective databases it will show up in the format i.e., 'July 14, 2005'.

Got that senoras and senoritas

Thanks
samu

[ray.wurlod]

And the important point is that this was done in a Transformer stage (not, for example, in a Modify stage).

[clshore]

Samu wrote:

For example: in source if i have '1050414' , i need to get it in form 'April 14,2005'

My point about the leading year was this:

If you have '1050414', convert and add 19000000, yielding 20040414.
Then convert that to 'April 14,2005'

Carter

[senthilmuruganv]

Hello,

I have two dates and format is TimeStamp and the database is Oracle. if I subtract the date from one date to another date, I used the following in transformation.

OConv(DateTimeStampToInternal(DSLink3.milestone_date) - DateTimeStampToInternal(DSLink3.milestone_capture_date), 'yyyy-mm-dd hh24:mi:ss')

But the result is not in the date format.

Please help me


Thanks,

Senthil.

[kcbland]

Welcome aboard. Since you're posting a new message for your own separate issue, please start a new message thread. That will force you to enter all of the relevant information, such as your version of DataStage, etc.

You're asking a Server question in the Parallel forum, so please repost there.

Thanks.