i have a question regarding handeling different date formats using ifthenelse:
i have this data flow where i exract from a csv file and one of the columns contain dates
the actual data comes in all sorts of different formats: 'mm/dd/yyyy h:mm:ss' or 'm/dd/yyyy h:mm:ss' 'm/dd/yyyy' or 'mm/dd/yyyy' etc
evetually i want all rows in format: mm/dd/yyyy hh:mm and i dont mind to add 00:00 in case the time is missing
i tried the following command at the columns' mapping:
ifthenelse(time(RESELLER_VERSION.ACTIVE_DATE) is null, RESELLER_VERSION.ACTIVE_DATE || '00:00',to_date(RESELLER_VERSION.ACTIVE_DATE, 'mm/dd/yyyy hh:mm'))
i recieved a validation error..
of course this command supports for now only one case but i think if that will work properly it's a good start