Hi, I'm not able to figure out how to convert a String to a date and get the previous day of that date in SSIS. Example - If this is the string Input - 20120327 I need the output to be 20120326 ( One day before ) Please help. Regards, James...

Hi James, Try using the below code select cast(left(20120327 , 4) + '-' + substring( cast(20120327 as varchar(10) ) , 5,2) + '-' + (right(20120327 , 2)) as datetime)-1 Thanks, Suresh Please use Mark or Propose as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

I am going to assume that you have exactly 8 characters in your date string and that it is always a valid date. Try this (replace @Source with the column or variable you are using): (DT_WSTR, 8) ((10000 * YEAR( DATEADD( "day", -1, (DT_DATE) ( SUBSTRING(@[User::Source],1,4) + "-" + SUBSTRING(@[User::Source],5,2) + "-" + SUBSTRING(@[User::Source],7,2) ) ))) + (100 * MONTH( DATEADD( "day", -1, (DT_DATE) ( SUBSTRING(@[User::Source],1,4) + "-" + SUBSTRING(@[User::Source],5,2) + "-" + SUBSTRING(@[User::Source],7,2) ) ))) + (1* DAY( DATEADD( "day", -1, (DT_DATE) ( SUBSTRING(@[User::Source],1,4) + "-" + SUBSTRING(@[User::Source],5,2) + "-" + SUBSTRING(@[User::Source],7,2) ) ))) ) Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008

Here is one kludgy way to do it. Add a derived colum block and enter a new column (DatePlusOne) with the expression DATEADD("day",-1,(DT_DATE)((SUBSTRING((DT_WSTR,8)DateField,5,2) + "-" + SUBSTRING((DT_WSTR,8)DateField,7,2) + "-" + SUBSTRING((DT_WSTR,8)DateField,1,4)))) Add another derived column block and enter a new column with this expression: (year(DatePlusOne) * 10000)+(month(DatePlusOne) * 100)+Day(DatePlusOne) It would be easier to just do it in a script transform via c# though

You could use a derived column for this or use one of the SQL examples if your source is a database. First cast it with substrings to an ISO format of YYYY-MM-DD. Something like: (db_date)(SUBSTRING([yourStringColumn], 1 , 4) + "-" + SUBSTRING([yourStringColumn], 5 , 2) + "-" + SUBSTRING([yourStringColumn], 7 , 2)) After that you can use a dateadd to get the day before: DATEADD("day",-1,[yourDate]) Combined it looks something like: DATEADD("day",-1,(db_date)(SUBSTRING([yourStringColumn], 1 , 4) + "-" + SUBSTRING([yourStringColumn], 5 , 2) + "-" + SUBSTRING([yourStringColumn], 7 , 2)))Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Another expression for you. Chose the one you like ;) REPLACE( SUBSTRING( (DT_WSTR,20) DATEADD("day",-1,(DT_DATE)(SUBSTRING(@[User::s],1,4) +"-"+ SUBSTRING( @[User::s],5,2) +"-"+ RIGHT(@[User::s],2))) , 1,10) ,"-","") My Blog | Ask Me | SSIS Basics

select REPLACE(convert(varchar(10),cast('20120327' as datetime) -1,120),'-','')

Hi Sudeep... If I have the date format as 2012-03-27 and want the output as 20120326 ( One day before ) how do I do it... regards, James.

Hi dschwant, I get an error while Iuse this in Derived Coulmn...it says varcahr is not recognized.

Hi Sudeep... If I have the date format as 2012-03-27 and want the output as 20120326 ( One day before ) how do I do it... regards, James. Just remove the part where I am setting up the "-" in the string: REPLACE( SUBSTRING( (DT_WSTR,20) DATEADD("day",-1,(DT_DATE) (@[User::s] )) , 1,10) ,"-","") My Blog | Ask Me | SSIS Basics

Hi Sudeep... If I have the date format as 2012-03-27 and want the output as 20120326 ( One day before ) how do I do it... regards, James. Just remove the part where I am setting up the "-" in the string: REPLACE( SUBSTRING( (DT_WSTR,20) DATEADD("day",-1,(DT_DATE) (@[User::s] )) , 1,10) ,"-","") My Blog | Ask Me | SSIS Basics