• 1 reply
  • Latest Post - ‏2012-04-16T16:24:52Z by Bobznkazoo
4 Posts

Pinned topic Impromptu text to date field conversion routine

‏2012-04-05T00:34:40Z |
Good day, eh.
I am mostly familiar with Cognos Impromptu but new to Powerplay and Transformer. We are in the process of converting from Cognos 7.x to 8.x but that process will take several months.
In the meantime I need to find a function or routine to convert a date that is defined as a char(10) field in a SQL Server database to a date field in Impromptu so I can add it to a Powerplay Cube. I need it as a date field so I can use it as a time dimension in the Transformer Dimension Map.

Does anyone have a routine using the available Impromptu 7.x functions to convert this text "date" into a date field? Is there another conversion option that I am missing? There is a make_datetime function, but that process is convoluted and Impromptu is balking at allowing me to write the entire routine. Please note that there is a text-to-date function in 8.x, but that is not an option at this time.

I appreciate any ideas folks have to offer.
Updated on 2012-04-16T16:24:52Z at 2012-04-16T16:24:52Z by Bobznkazoo
  • Bobznkazoo
    4 Posts

    Re: Impromptu text to date field conversion routine

    There are two ways to solve this issue:
    I was able to define the data type of the original text column as being a date in Transformer in the properties info of the column (under the general tab), and then specify the input format of the date on the Time tab. The problem I had was that I had not specified the input format.

    But for the long term, we need to convert the field to a date format within Impromptu. That way should someone else use the query it will be ready. As a solution, I've found the correct combination of functions to convert the field:

    If ( string-to-integer ( left ( ManufactureDate, locate ( '/'. ManufactureDate) ) ) 0 ) then ( make-datetime ( string-to-integer (right ( trim-trailing ( ManufactureDate), 4) ), string-to-integer ( left ( ManufactureDate, locate ( '/', ManufactureDate ) ) ) , string-to-integer ( substring ( ManufactureDate, locate ('/', ManufactureDate) +1 , 2) ) ) ) else NULL