Error converting data type varchar to datetime

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Error converting data type varchar to datetime

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-02-27 : 06:59:10

I have the following:

Execute SQL Task: Selects Dates into a Result Set
ForEach loop: Takes each date and stores in a variable
Execute SQL Task: SQL statement is a variable (SQLSourceType = variable), and the variable is a string data type that generates the following expression:

"EXEC admin.CreateTablePartitions "+ "'"+(DT_WSTR, 30) @[User::PartitionDate]+"'"

This evaluates to:
EXEC admin.CreateTablePartitions '27/02/2009'

But when I try and execute, I get the following error:

Execute SQL Task: Executing the query "EXEC admin.CreateTablePartitions '24/10/2008'" failed with the following error: "Error converting data type varchar to datetime.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

WHHHHHHHYYYYYY?!?!?

Hearty head pats

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-27 : 07:51:17

If parameter to procedure CreateTablePartitions is DATETIME, try to convert and format date in ISO format yyyymmdd.
As of now, your SSIS package tries to interpret 27 as month value which does not exist to my knowledge.

E 12°55'05.63"
N 56°04'39.26"

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-02-27 : 08:33:53

Thanks Peso

Works now!

Hearty head pats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources