convert string to timestamp am or pm

vilasini · Post by **vilasini** » Wed Jan 09, 2008 5:25 am

Hi Friends,

I have timestamp filed with varchar datatype and I need to convert it to timestamp with different format mm/dd/yyyy and hours should be 12 hrs basis am or pm.
Can any one suggest.
Thanks in advance.

Regards
Anju

ArndW · Post by **ArndW** » Wed Jan 09, 2008 5:35 am

timestamp and date columns have no format. Formatting is only necessary when converting to a string type. You can do a

Code: Select all

TimeStampToString(In.TimeStampColumnName,'%mm/%dd/%yyyy %HH:%nn:%ss %aa')

Maveric · Post by **Maveric** » Wed Jan 09, 2008 5:41 am

Timestamp is an internal DataStage type and you cannot format it or add am or pm to it. However if you go to job properties and defaults tab, what options do you get in the drop down for the timestamp format?

nkreddy_3 · Post by **nkreddy_3** » Wed Jan 09, 2008 6:05 am

Hi,

As per my knowledge the AM and PM will depends on the database. Datastage has it's own format that you can see from in the defaults tab of job parameters

Thanks,
Karunakar

vilasini · Post by **vilasini** » Wed Jan 09, 2008 6:08 am

Yes The default format is yyyy-mm-dd hh:nn:ss.So can't I change the format with am or pm to timestamp.I tried StringToTimestamp and all.But I can not.Suggest me on this.

Thanks

ArndW · Post by **ArndW** » Wed Jan 09, 2008 6:14 am

<sigh> vilasani, what happened when you tried TimeStampToString?

vilasini · Post by **vilasini** » Wed Jan 09, 2008 6:24 am

Can I use Iconv and Oconv functions.If it is so how to use those functions.

Thanks
Anju

nkreddy_3 · Post by **nkreddy_3** » Wed Jan 09, 2008 6:34 am

Are you working on Server jobs or Parellel Jobs?

vilasini · Post by **vilasini** » Wed Jan 09, 2008 6:49 am

Can I use Iconv and Oconv functions.If it is so how to use those functions.

Thanks
Anju

vilasini · Post by **vilasini** » Wed Jan 09, 2008 6:51 am

Can I use Iconv and Oconv functions.If it is so how to use those functions.

Thanks
Anju

ArndW · Post by **ArndW** » Wed Jan 09, 2008 7:01 am

Vilasini - unless you answer the question you won't get any answers!

ICONV/ICONV don't exist in EE/PX jobs, but you posted the question in this forum so that is why nkreddy_3 asked what kind of job you have.

Maveric · Post by **Maveric** » Wed Jan 09, 2008 7:02 am

You can use Iconv and Oconv in a basic transformer in parallel jobs. But i am quite sure it will not give the desired results. And also performance will be effected because the basic transformer will work in sequential mode. Why not change the data type to String and try as Arndw has suggested.

Maveric · Post by **Maveric** » Wed Jan 09, 2008 7:04 am

You can use Iconv and Oconv in a basic transformer in parallel jobs. But i am quite sure it will not give the desired results. And also performance will be effected because the basic transformer will work in sequential mode. Why not change the data type to String and try as Arndw has suggested.

vilasini · Post by **vilasini** » Wed Jan 09, 2008 7:16 am

I am using parallel jobs.I have used SringToTimestamp alone it works.But I need mm/dd/yyyy with am or pm based hrs.
Any one can suggest.

Thanks
Anju.

ArndW · Post by **ArndW** » Wed Jan 09, 2008 7:37 am

Vilasini - I now give up.
You won't post what you have tried and what the results are when things don't work.
I gave you a transform stage derivation that will work (since I tested it), but you seem to prefer to ignore minor matters like that so I wish you luck in getting this solved.