Playing with Date-type data

kumarjit · Post by **kumarjit** » Thu Dec 26, 2013 1:46 am

I have a requirement , something like this :

In our parallel job, we need to read a source file , one of the fields of which contain data like :
08/24/2012 23:11:15

What we intend is to transform it to a format like :
2012-08-24 11:11:15 PM

I was looking ahead to use the OCONV function(as because I want to display the AM/PM portions also , which calls in for a tranformation that can yield data as per a custom external format) to acheive this , but seriuosly , no idea at all about the arguments of the fucntion.

Any help is truly appreciated.

Regards
Kumarjit.

chulett · Post by **chulett** » Thu Dec 26, 2013 8:09 am

You can't use I/OConv as they are Server functions. You should be able to use StringToTimestamp however are you certain you need it in 12 hour AM/PM format?

kumarjit · Post by **kumarjit** » Fri Dec 27, 2013 7:16 am

StringToTimeStamp works good only if the DELIMETER in the date field is "-" (as set at Project level) and not "/" (as is in my case).
And yes , the 12 hour format (AM/PM) is required (that is the core of this requirement).

Regards
Kumarjit.

chulett · Post by **chulett** » Fri Dec 27, 2013 7:41 am

StringToTimestamp works with any delimiter. You specify it in the format string you pass to the function.

chulett · Post by **chulett** » Fri Dec 27, 2013 9:17 am

Clarify for us the data type of the target - is it specifically a Timestamp or is it another string? For the latter use TimestampToString after the initial conversion and use '%(HH,u)' to get it back to a string with the time in 12 hour format with an upper case AM/PM.