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

avatar image

7 2 2 3

(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

avatar image

Grant Fritchey ♦♦
137k 20 47 81

(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

avatar image

Fatherjack ♦♦
43.8k 79 102 118

(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

avatar image

Todd McDermid
391 2 3

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

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: 9630 times

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

Copyright 2018 Redgate Software. Privacy Policy