Click here to Skip to main content
14,327,370 members
Rate this:
Please Sign up or sign in to vote.
See more:
Im going crazy, I'm transferring some data bases on two sqlserver 2008R2 the first and the second 2005.
Being in Italy for transfer using the format 'yyyy-mm-dd hh: mm: ss: nnn'
The DTS have in query transform for fields that are datetime specification:
cast ('<value>' as datetime)
and for those smalldatetime use
cast ('<value>' as smalldatetime)
, where in both cases the '<value> 'is in the format indicated above.
On SQLServer 2008R2 everything is OK, while on SQLServer 2005 I always reports that the date value is outside the allowed range.
I have verified that both servers use the same SQL COLLATE and that the base language is the same (italian) and that the regional settings are correct.
I can not understand, I have tried in the properties if there is any particular specification, but have not found anything.
Someone can give me the right direction to solve this problem.

CHill60 13-Oct-14 4:31am
A date is a date is a date. This seems to imply that you're sorting dates as varchars?
Rate this:
Please Sign up or sign in to vote.

Solution 1

You need to use SET DATEFORMAT[^] comand ;) to temporary change format used by sql server.
For example:
SELECT CAST(<FieldName> AS DATETIME) AS ProperDateTime

If you change it, you'll be able to import/export data ;)

Here, on CP board, you'll find tons of answers to the question similar to yours[^].
Rate this:
Please Sign up or sign in to vote.

Solution 2

it's works fine.
I've never used this instruction
Maciej Los 13-Oct-14 5:18am
You're welcome ;)
Please, delete this answer to avoid down-voting. It's not solution. It's comment. Use "Have a question or comment" widget to post comment.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100