DATETIME difference. One client accepts 2009-06-30 in a script file, the other client doesnt accept it but accepts 2009-30-06.

Question

DATETIME difference. One client accepts 2009-06-30 in a script file, the other client doesnt accept it but accepts 2009-30-06.

holtshousens

SSC Enthusiast

Points: 128
More actions
July 7, 2009 at 2:12 am

#139638

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 WHERE RecordDate = '2009-06-30';
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?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

BrainDonor SSCoach Points: 19252 More actions · Answer 1

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.

Steve Hall
Linkedin
Blog Site

Dave Ballantyne SSC-Dedicated Points: 33566 More actions · Answer 2

Use all dates in the format YYYYMMDD , note not YYYY-MM-DD

Two links for you

http://www.karaszi.com/SQLServer/info_datetime.asp

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/01/05/date-format-inconsistency-yyyy-mm-dd-or-yyyymmdd-when-2007-04-10-is-4th-october.aspx

Clear Sky SQL
My Blog[/url]

holtshousens SSC Enthusiast Points: 128 More actions · Answer 3

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.

NazerMohamed Mr or Mrs. 500 Points: 502 More actions · Answer 4

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