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.
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:
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: