CODEWITHSUNDEEP
ssis
Moe Sisko
Moe Sisko

Reputation: 12051

SSIS expression to convert string to date

Using SQL Server 2008, Visual Studio 2008.

Trying to convert the data from a string column into a derived Date column. The string can be in two formats, either : "dd/mm/yy" (where century 20 can be assumed), or "dd/mm/yyyy" . e.g. "02/05/12", "23/12/02", "13/08/2012".

So I tried an expression like this for a Derived column : 

DateReceived == "" ? NULL(DT_DATE) : (DT_DATE)((LEN(RTRIM(DT_DATE)) == 10 ? SUBSTRING(DateReceived,7,4) : ("20" + SUBSTRING(DateReceived,7,2))) + "-" + SUBSTRING(DateReceived,4,2) + "-" + SUBSTRING(DateReceived,1,2))

but Visual Studio gives error : "The expression was not valid, or there is an out-of-memory error".

It accepts a simpler expression like : 

DateReceived == "" ? NULL(DT_DATE) : (DT_DATE)("20" + SUBSTRING(DateReceived,7,2) + "-" + SUBSTRING(DateReceived,4,2) + "-" + SUBSTRING(DateReceived,1,2))

but this expression will only work for "dd/mm/yy" format.

What am I doing wrong ? Thanks.

Upvotes: 1

Views: 8827

Answers (1)

Moe Sisko
Moe Sisko

Reputation: 12051

Never mind, the error was due to a typo. (Was calling RTRIM on the column type, not column name).

The following works :

DateReceived == "" ? NULL(DT_DATE) : (DT_DATE)((LEN(RTRIM(DateReceived)) == 10 ? SUBSTRING(DateReceived,7,4) : ("20" + SUBSTRING(DateReceived,7,2))) + "-" + SUBSTRING(DateReceived,4,2) + "-" + SUBSTRING(DateReceived,1,2))

Upvotes: 2

Related Questions