Search code examples
sqldatemulederby

Mule/Apache Derby - convert date retrieved in SELECT query from MM/DD/YYYY to DD.MM.YYYY

I have a Mule application that is retrieving information from an Apache Derby database. One field being pulled is a date in the format MM/DD/YYYY - this field is stored as a VARCHAR(30).

How can I query this table and retrieve the date field in the format DD.MM.YYYY? - note the need to transform the dividers to '.' to '/'. Alternatively, is there is a way I can perform this action using Mule Expression Language? Thanks in advance.

Solution

  • The solution I found to this was to do the transformation in SQL with a series of concatenated substrings:

    SUBSTR(MyDate,4,2) || '.' || SUBSTR(MyDate,1,2) || '.' || SUBSTR(MyDate,7,4)