Need help in converting Date time to Timestamp Format
Moderators: chulett, rschirm, roy
Need help in converting Date time to Timestamp Format
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
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
Re: Need help in converting Date time to Timestamp Format
I m not realy good but I did the same with many if elsefun4work wrote: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
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
-
- Participant
- Posts: 437
- Joined: Fri Oct 21, 2005 10:00 pm
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.
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.
Keith Williams
keith@peacefieldinc.com
keith@peacefieldinc.com
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
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
DSguru2B wrote: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.
-
- Participant
- Posts: 437
- Joined: Fri Oct 21, 2005 10:00 pm
Agreed that's why I stated in addition to what you were asking.chulett wrote:Still think it would help if you answered my questions.
Keith Williams
keith@peacefieldinc.com
keith@peacefieldinc.com
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.
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.
DSguru2B wrote: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. ...
-
- Participant
- Posts: 437
- Joined: Fri Oct 21, 2005 10:00 pm
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.
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.
Keith Williams
keith@peacefieldinc.com
keith@peacefieldinc.com
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
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