Converting VARCHAR value to SMALLDATETIME in SQL 2005 (SSIS)

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

more ▼

asked Feb 10, 2010 at 08:57 AM in Default

SQLBeginner587 gravatar image

7 2 2 2

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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:

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.

more ▼

answered Feb 10, 2010 at 09:17 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.6k 19 21 74

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, 2010 at 11:40 AM SQLBeginner587
Excellent. Glad I could help.
Feb 10, 2010 at 12:07 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Feb 10, 2010 at 09:02 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.4k 75 78 108

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Feb 10, 2010 at 01:16 PM

Todd McDermid gravatar image

Todd McDermid
391 2

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Feb 10, 2010 at 08:57 AM

Seen: 6368 times

Last Updated: Feb 10, 2010 at 08:57 AM