Click here to Skip to main content
14,327,370 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi,
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.
thanks


		    
Posted
Comments
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:
SET DATEFORMAT dmy;
--or
--SET DATEFORMAT ymd;
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[^].
   
v3
Rate this:
Please Sign up or sign in to vote.

Solution 2

Hi,
it's works fine.
I've never used this instruction
Thanks
   
Comments
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