SELECT statement with dates works on one machine but not on another
Posted on 2007-07-20
I have 4 machines all running their own SQL databases. The following statement:
SELECT CAST(Convert(char, DATEADD(dd,1,getdate()), 101) as smalldatetime)
gives the following error on one machine:
"The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value".
This error only began recently, so I think I must have inadvertently changed the default DATE STYLE/FORMAT or something on this one problem machine. I think this is something to do with the default DATESTYLE on this one particular machine because if I change the format in this statement to 103, ie SELECT CAST(Convert(char, DATEADD(dd,1,getdate()), 103) as smalldatetime) ...then it returns 2007-07-21 00:00:00.000 (the same result as the other 3 machines). Problem is, it is happening on my main development machine so I have to build solutions and test them on one of the other machines - hardly practical!! :D I need all machines to have the same default date style.
I am aware of the many functions such as DATEPART for dealing with different date formats, but there must be something I can change on this problem machine to make it behave the same as the others.
500 points cos this is really beginning to hold me up!
Thanks in advance...