Derived Column / Change date format

Hi,

Is there a way using a derived column transformation to change the format of the date eg:

The input flat file's date is in yyyy/mm/dd and i need to convert / change this to yyyy/dd/mm

Is this possible?

Thanks in advance,

Slash.

Substring and concatenation, perhaps?|||.. would work because it is not really a date is it? It is just a string. Worth bearing in mind since display of dates is really a client issue not a SSIS/Server issue, unless you are taking a string and converting to a date type. Then SSIS will use the "client" as in user account settings that the process runs under when interpreting the string.|||

The weird thing is that the original flat file date is all yyyy/mm/dd but when it comes to import into the database as datetime. It inconsistently changes the format of the date some dates appear correct and some appear as yyyy/dd/mm. Is there anyway to over come this / have you heard of anything like this before?

Thanks for the replies,

Slash.

|||

slash85 wrote:

The weird thing is that the original flat file date is all yyyy/mm/dd but when it comes to import into the database as datetime. It inconsistently changes the format of the date some dates appear correct and some appear as yyyy/dd/mm. Is there anyway to over come this / have you heard of anything like this before?

Thanks for the replies,

Slash.

The database isn't returning multiple formats once inserted... That is, when doing a select from a database table, you aren't seeing multiple formats in a datetime field. It's impossible. If the destination field is a character field, and you're converting to datetime before inserting, then perhaps you have some invalid dates that cannot convert.

To my knowledge, without converting to a string, and using the substring function, you will not be able to get the format you desire.