Geeks With Blogs

Arthur Zubarev Compudicted

I had another fun day at the SSIS MSDN Forum today.

It came to my attention that many people come with date-time formatting and conversion issues.

Indeed, there are not so many reference articles around and a myriad of date formats out there.

One of the interesting topics today was on DDMONYYYY date conversion.

Most answerers suggested to use the Script Task to manipulate on dates, but I was not satisfied with the “only” answer.

So, even though I like the power of the script, I was thinking there should be an “Expression Way” to solving this.

Turned out there is an easy one:


(FINDSTRING( @[User::StringVar], "JAN", 1 ) >1 ? SUBSTRING( @[User::StringVar], 6, 5 ) + SUBSTRING( @[User::StringVar], 1, 2 ) + "01" : "") +
(FINDSTRING( @[User::StringVar], "FEB", 1 ) >1 ? SUBSTRING( @[User::StringVar], 6, 5 ) + SUBSTRING( @[User::StringVar], 1, 2 ) + "02" : "") +
(FINDSTRING( @[User::StringVar], "MAR", 1 ) >1 ? SUBSTRING( @[User::StringVar], 6, 5 ) + SUBSTRING( @[User::StringVar], 1, 2 ) + "03" : "") +
(FINDSTRING( @[User::StringVar], "APR", 1 ) >1 ? SUBSTRING( @[User::StringVar], 6, 5 ) + SUBSTRING( @[User::StringVar], 1, 2 ) + "04" : "") +
(FINDSTRING( @[User::StringVar], "MAY", 1 ) >1 ? SUBSTRING( @[User::StringVar], 6, 5 ) + SUBSTRING( @[User::StringVar], 1, 2 ) + "05" : "") +
(FINDSTRING( @[User::StringVar], "JUN", 1 ) >1 ? SUBSTRING( @[User::StringVar], 6, 5 ) + SUBSTRING( @[User::StringVar], 1, 2 ) + "06" : "") +
(FINDSTRING( @[User::StringVar], "JUL", 1 ) >1 ? SUBSTRING( @[User::StringVar], 6, 5 ) + SUBSTRING( @[User::StringVar], 1, 2 ) + "07" : "") +
(FINDSTRING( @[User::StringVar], "AUG", 1 ) >1 ? SUBSTRING( @[User::StringVar], 6, 5 ) + SUBSTRING( @[User::StringVar], 1, 2 ) + "08" : "") +
(FINDSTRING( @[User::StringVar], "SEP", 1 ) >1 ? SUBSTRING( @[User::StringVar], 6, 5 ) + SUBSTRING( @[User::StringVar], 1, 2 ) + "09" : "") +
(FINDSTRING( @[User::StringVar], "OCT", 1 ) >1 ? SUBSTRING( @[User::StringVar], 6, 5 ) + SUBSTRING( @[User::StringVar], 1, 2 ) + "10" : "") +
(FINDSTRING( @[User::StringVar], "NOV", 1 ) >1 ? SUBSTRING( @[User::StringVar], 6, 5 ) + SUBSTRING( @[User::StringVar], 1, 2 ) + "11" : "") +
(FINDSTRING( @[User::StringVar], "DEC", 1 ) >1 ? SUBSTRING( @[User::StringVar], 6, 5 ) + SUBSTRING( @[User::StringVar], 1, 2 ) + "12" : "")

* The above expression assumes that the input date has leading zeroes (e.g. 01MAR2010).

The result is 01032010.

Posted on Tuesday, October 5, 2010 10:28 PM | Back to top


Comments on this post: How to Convert a DDMONYYYY Date to an ISO Date in SSIS

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © Compudicted | Powered by: GeeksWithBlogs.net