DSXchange

A Date datatype doesn't have a format. You don't have a Date, you have a string in "dd/mm/yyyy" format. That's why it works when you declare it as a Varchar.

Hi,

If you are sourcing from a Sequential File, goto the file properties tab where you will find a DATE sub-tab. Browse that tab and you will find 'FORMAT' option. If you click it, you will be able to see the format of your date field. By default it is 'mm/dd/yyyy'. You can change this format to whatever format you want.

Other approach to your problem can be, try converting your source DATE to CHAR and then apply the Substrings() you want on it.

No, you cannot change the format to "whatever you want", it needs to match what's in the file to convert properly. Regardless, won't help here. And the OP has already reported success treating the field as a string.