Anyone that can help solving the following problem. Need to load date values into SQL 2005 with SSIS:
The format of the source data is the following: "1997-09-01" [DateStringColumn; varchar (10)] Need to convert this to smalldatetime datatype in this format (yyyy-mm-dd)
My dataflow in SSIS contain the following query:
SELECT CAST(CONVERT(varchar(10),DateStringColumn) AS smalldatetime, 120) AS DateResultColumn FROM ....
Preview option in the dataflow (source editor) display the results still in this format: 9/1/19997 12:00:00 AM (i.e. m/d/yyyy format)
Problem: I now receive an error with the following message "conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value" - error in the SSIS dataflow task; when loading this to the destination DateResultColumn [with smalldatetime datatype]
Also note: The DateStringColumn does not contain any dates prior to 1900/01/01; these were allready set to a default value of 1900-01-01 if the original values was e.g. 0001-01-01.
Any help will be much appreciated. Regards, SQLBeginner587