How to Convert a String to Date in SSISby Vivek Saxena
Converting a string to a date in SQL Server Integration Services (SSIS) requires using functions, which are integrated programming modules designed to automatically complete a task, such as converting a string to a date or performing arithmetic calculations like addition or subtraction. The only catch, however, relates to the way in which you use functions. It all depends on whether you're converting a string, a string variable, or a string query.
Convert a a string in the form of YYYY-MM-DD to a date by using the function DT_DBDATE. YYYY refers to the year, MM refers to the month and DD refers to the date. Example: (DT_DBDATE) "2011-07-04"
Use the function DT-DBDATE in conjunction with a properly configured string variable to convert it into a date. Make certain the value within the variable is in the form YYYY-MM-DD, where YYYY refers to the year, MM refers to the month and DD refers to the date. Example with string variable TodaysDate: (DB_DBDATE) TodaysDate
Wrap the query variable in the DT-DBDATE function when you're trying to retrieve a value from the database and automatically convert it to a date. Make certain that the value you're retrieving is already in the form YYYY-MM-DD. Otherwise you will encounter an error. Example with the string variable TodaysDate: Select DT_DBDATE(TodaysDate)