Datetime error

ORDNO is string which include the date, like 'XX021224XXXXXXX'.
I use SUBSTRING() to get the date part from this string.
 

What error do you get exactly on the "other environment"?

Anthony

Give the data present in ORDNO

I'm useing the Japanese version of SQLserver,
so I don't know the exact error message in English.
If I try to translate in English, it's like 'The result value of the conversion from char to datetime is over the range of datetime value'.(message 242, level 16)

Do you have any date in ORDNO less than 12/12/1752
I dont think so..
but only in such conditions, you will get this kind of error
'The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.'

Coz' datediff will return an integer which is out of the range for that integer datatype

I got the table data from 'the other environment'
and tried the sql with that data on my computer.
I didn't get any error.

So I don't think there is any data which couses this error...

You can also refer this site..

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=9147&pg=2
in which you can set the format for the datetime values.

Thank you for the information.

I'm sorry to ask you again.
Because of my poor English skill,
but I don't get the exact meaning of what the page says.

The first page you listed is saying that
the error occurs when the database server is using
a non-US date format, right?

Though, our system is using a US date format(yy/mm/dd)...

are both systems using the same date format?

As if with the same data you are getting an error on 1 system but not on the other then it is probably a server setup problem

if this is already mentioned in the links someone has posted then ignore me.

Matt.

Hi mini1111

1. It is more than likely that the error is caused by different date settings in the SQL database.  Try to use convert(datetime,'2002/12/22',111) instead of the string alone ('2002/12/22').  This will send the date as datetime but not as string and will thus work with any date settings on the server.  111 means the string is in the format yyyy/mm/dd.

2. The convert(datetime, substring(d.ORDNO,3,6)) is probably causing problems too.  You are not passing the third parameter to the convert function, wich specifies the format of the datestring.  In your case this should look like convert(datetime, substring(d.ORDNO,3,6),12) since 12 means the format yymmdd.

Look at the Transact-SQL help on the CONVERT function.

A word of advice too.

Avoid using functions on fields like this: substring(ORDNO,3,6).  This will make the index on the field useless and on large tables queries like this will run very slow.

Best regards,
bragn.

Thank you for all the information.
I think I could figure out the solution.

There existed a difference between two environment.
The one had been added the index on the PATID field.
Because of this index, the optimization occured and this
made the sql to cause the error.

I don't think this is the exact solution,
but at least the index did bad things of some kind.