Converting VARCHAR value to SMALLDATETIME in SQL 2005 (SSIS)

Question

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

Answer 1

0

It's possible that you have data in the database that isn't date compliant. You could run a quick validation against the data using:

SELECT PK,
       DateStringColumn 
FROM TableName
WHERE ISDATE(DateStringColumn) = 0

If you have to, you put in an error handler in the SSIS stream to dump the bad values out to an error table.

answered Feb 10 '10 at 09:17 AM

Grant Fritchey ♦♦
62.7k ● 12 ● 20 ● 66

Thanks Grant- I did not expect any "out-of-range" values to be included in the source data. But I've managed to find a single record with incorrect date value that was entered. Your quick validation suggestion really helped.

Feb 10 '10 at 11:40 AM SQLBeginner587

Excellent. Glad I could help.

Feb 10 '10 at 12:07 PM Grant Fritchey ♦♦

(comments are locked)

Answer 2

0

have you tried simply removing the - characters with REPLACE(DateStringColumn,'-','')? Thats the ISO format and may well work for you ...

answered Feb 10 '10 at 09:02 AM

Fatherjack ♦♦
38.8k ● 55 ● 69 ● 104

(comments are locked)

Answer 3

0

You should configure the error output of the Source to divert the rows that cause errors to the error output. Place a Multicast, Union All, Row Count, or other meaningless transform on the error output, and a Data Viewer on the connector. Now you'll know exactly what kind of values you're dealing with.

Second, the date WITH dashes is the ISO standard format. Without separators, the string is completely ambiguous. The 120 in the CAST statement specifically identifies the format as yyyy-mm-dd anyway.

Check Converting Strings to Dates with the Derived Column Component in SSIS.

answered Feb 10 '10 at 01:16 PM

Todd McDermid
391 ● 2

(comments are locked)

Converting VARCHAR value to SMALLDATETIME in SQL 2005 (SSIS)

Follow this question

Related Questions