Need help in converting Date time to Timestamp Format

[fun4work]

HI there,
Need to change the date time Input "Jul28 2010 12:30PM" to Timestamp <without seconds format>

I have tried all functions present in Transformer but NO Luck..

Please advise.

Thanks
Ravi

[behrouz]

HI there,
Need to change the date time Input "Jul28 2010 12:30PM" to Timestamp <without seconds format>

I have tried all functions present in Transformer but NO Luck..

Please advise.

Thanks
Ravi

I m not realy good but I did the same with many if else
so you split all your data
monthmyear,day and etc....


and for month you creat your eg:if mnth=jan then '01' else if ...=12
and at last you concatenet all then you do string to date

thats what I did

But I think you can do it easely by oconv iconv not sure

Regards
Arash

[chulett]

IConv/OConv would be a Server solution. I'm curious, is that an accurate representation of your incoming data? Specifically there's no space between the month and day? Is the month name always a fixed length of three?

[kwwilliams]

Along with Craig's questions:

What are your source and targets? If you have a database as a source or target you can use the date functions within the database engine to convert your MonDD YYYY HH:MM to a format more easily handled in DataStage.

If not you will have to:

1. Sustring the different pieces together as a previous poster referenced

or

2. You can write a custom routine to perform the conversion for you. This would be recommended if this would be common functionality for your company.

[DSguru2B]

I am guessing its a string field and not an actual date time?
If yes then a custom routine is in order or perhaps a simple server job to just do the date time conversion.

[fun4work]

Thanks everyone for replying.
some facts

1) I am getting data like this<in Date time format and not as a string> from Files and need to read the file into the Table<NETEZZA> by using datastage job.
2) I have almost 30-40 jobs where I need to read the data,
3) right now I am resding the Input data by VARCHAR field and trying to convert it into Timestamp<String to timetsamp> but is not working and I am getting *****.
4) I am in Parallel version 7.5.2

I am guessing its a string field and not an actual date time?
If yes then a custom routine is in order or perhaps a simple server job to just do the date time conversion.

[DSguru2B]

When I said its a string field, I meant you have defined it as a varchar/char/string within datastage.
Is there a to_timestamp() function for netezza? If yes then that would be your easiest solution.

[chulett]

Still think it would help if you answered my questions.

[kwwilliams]

Still think it would help if you answered my questions.

Agreed that's why I stated in addition to what you were asking.

[fun4work]

sorry about that,
month will be fixed <3 char> only

Still think it would help if you answered my questions.

Agreed that's why I stated in addition to what you were asking.

[fun4work]

I use Netezza stage to load data into table.
so bascially job loks simple as in:
Seq file--> Transformer--> Netezza stage to load table.
Now netezza stage doesn;t give option to change or apply some transformation while loading
also the function to_timestamp() doesn't exists in Netezza.

When I said its a string field, I meant you have defined it as a varchar/char/string within datastage.
Is there a to_timestamp() function for netezza? If yes then that would be your easiest solution. ...

[kwwilliams]

What created your sequential file? Can it be changed to create a timestamp that looks like YYYY-MM-DD HH:MM?

Otherwise you should write a custom routine to change your format to a timestamp because you are going to use this in over 40 jobs you don't want to have each job responsible for its own conversion.

[fun4work]

Hi William,
I tried to change the input to 'YYYY-MM-DD HH:MM' format but no luck.
I am liking Idea of creating a routine and then incorporate it in each job instead of putting the logic in all jobs.
can you please throw some light on the custom routine or probably an example will definitely help me.

Thanks
Ravi