On Tue, 31 May 2005 14:31:19 +0200, Zibi wrote:
I have some problem with datatime.
SELECT * FROM stat WHERE data > '2005-05-24 14:07:28' ORDER BY id Asc
I got the error:
Microsoft OLE DB Provider for SQL Server error '80040e07'
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
Why? Format of date is the same in database?
Hi Zibi,
Assuming that "data" is declared as a [small]datetime column, then it
has no format in the database. The internal representation of datetime
is, in fact, a set of two integers (but the internal representation is
in fact not relevant).
For your query, the date/time constant is first converted to the
internal representation of either datetime or smalldatetime (to match
that of the "data" column), then the comparison is made. Obviously, the
first part (the conversion of '2005-05-24 14:07:28' to [small]datetime)
is the cause of your error. Obviously, some locale settings on your SQL
Server make it think that you use a yyyy-dd-mm hh:mm:ss format.
To prevent this kind of errors, use only the guaranteed safe formats for
date and date/time constants:
* yyyymmdd (date only - note: no dashes, slashes, dots or whatever)
* yyyy-mm-ddThh:mm:ss (date plus time - note: dashes between the
components of the date; colons between the components of the time and an
uppercase T to seperate date from time)
* yyyy-mm-ddThh:mm:ss.ttt (as above, but with milliseconds, seperated
from the time by a dot).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
