Geeks With Blogs
Scott Lock Thoughts on .Net, Caparea.net and Windows Phone

I ran accross a field in a flat file yesterday that was in the format of "CCYYDDD".  Now I can only assume that "CC" means century, but that's not the point.  The actual value was "2008294".  I needed to write an expression that converted the year and day of year format to a standard date.  Here's the expression to do this (thank you to Ed Buhain for brainstorming):

DATEADD("d", ((DT_I4)SUBSTRING(Date, 5, 3) - 1), (DT_DATE)("1/1/" + SUBSTRING(Date, 1, 4)))

Where "Date" is the value you need to convert.

Pretty straight forward.  Just subtact 1 from the day of year value and add to January 1st using DateAdd.  The end result in this case is "10/20/2008"

 

Posted on Friday, October 31, 2008 9:48 AM SQL Server , SSIS | Back to top


Comments on this post: Converting Day of Year and Year to Date in SSIS

# re: Converting Day of Year and Year to Date in SSIS
Requesting Gravatar...
Elegant and simple. Nice.
Left by Marc Brooks on Feb 13, 2009 2:30 PM

Your comment:
 (will show your gravatar)


Copyright © Scott Lock | Powered by: GeeksWithBlogs.net