July 7, 2009 at 2:12 am
I have come across the following error:
Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.
code i ran was like so:
DELETE FROM
when i run this on one client machine it works fine. When i run it on another client machine i get the above error. I have found that if i change the date to: '2009-30-06' on the one machine it solves the problem.
Any ideas of why this is happening and if there is a setting which could be causing this on the one machine?
July 7, 2009 at 4:42 am
Do the Regional Settings of that Client machine match the Regional Settings of the Server?
I used to work for a company that had customers across Europe and USA, so we tended to format the dates as '30 Jun 2009', which SQL Server liked regardless of the date formatting on the server.
BrainDonor.
July 7, 2009 at 5:03 am
Use all dates in the format YYYYMMDD , note not YYYY-MM-DD
Two links for you
July 7, 2009 at 7:06 am
Thanks for the replies
I have checked regional settings on both machines and they are the same.
If i change date format to YYYYMMDD instead of YYYY-MM-DD then i will have hundreds :w00t: of SPs and Triggers which will need to be updated.
There must be a simple explaination for this.
July 7, 2009 at 7:24 am
try
SET DATEFORMAT YMD
DELETE FROM WHERE RecordDate = '2009-06-30';
This forces to date format to YY-MM-DD, ignoring the client Regional settings.
This will do a quick fix. But performance wise, using set command is not advisable. It forces not to cache the query plan. So if you are dealing with heavy number of requests executing the same query, then you might have to think about it.
NM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply