Good day, eh.
We are using Impromptu 7.4 and 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 the field as a time dimension in the Transformer Dimension Map.
Does anyone have a routine using the available Impromptu 7.4 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.
You shouldn't need to do this! You can define the data type of the original text column as being a date from within Transformer in the properties of the column (general tab), and specify the input format of the date on the Time tab. Transformer will then treat this in the same way as a database date/time column.
Regards,
MF.
Thanks for the answer. That worked great!
But for the long term, we need to convert the field to date format within Impromptu. That way should someone else use that 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