Skip to Content
Former Member
Nov 28, 2012 at 02:57 PM

case for different date formats


hi everyone,

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