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?

Regards,

Hi
just try it this way:

SELECT *
FROM stat
WHERE
data > convert(varchar(10),'2005-05-24 14:07:28',101) ORDER BY id Asc

best Regards,
Chandra
http://groups.msn.com/SQLResource/
http://chanduas.blogspot.com/
---------------------------------------

*** Sent via Developersdex http://www.developersdex.com ***

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)

On Tue, 31 May 2005 15:01:55 GMT, Chandra wrote:

>
>Hi
>just try it this way:
>
>SELECT *
>FROM stat
>WHERE
>data > convert(varchar(10),'2005-05-24 14:07:28',101) ORDER BY id Asc
>
>best Regards,
>Chandra

Hi Chandra,

This won't work, for two reasons.

First: if data is a datetime column (which I hope it is - otherwises,
the OP has a bag of other problems), then converting the constant to
varchar won't do any good. It is just an extra conversion to slow down
the process; in the end, it'll be converted to datetime in order to make
the comparison.

Second: the expression
convert(varchar(10),'2005-05-24 14:07:28',101)
returns the string constant '2005-05-24'. Since you're converting a
varchar constant to varchar, the stylle parameter is not used; you
simply get the first 10 characters. As a result, the time portion in
stripped and the query will return too many rows.

Third: since the format yyyy-mm-dd is not guaranteed safe either, this
version might result in the same error as well.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes:
> 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.

Actually, this happens if you have a SET DATEFORMAT dmy somewhere,
explicitly or implicitly. While ymd is possible to set, it's rarely
used in practice. dmy, on the other hand is common with many
language settings.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Uzytkownik "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> napisal w
wiadomosci news:rhfp91pppbovern3umptsgr3kimkai7ie1@4ax.com...
> On Tue, 31 May 2005 14:31:19 +0200, Zibi wrote:

>
> * 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).
>
Hi,

Thanks all.
I use exctly - SELECT COUNT(id) AS [stat_ile] FROM stat WHERE (data >
CONVERT(DATETIME, '2005-05-24 14:07:28',101)) and it works. I don't need to
use hours. I found that format in database is yyyy-mm-ddThh:mm:ss.ttt when I
use query analyzer but when I use simple SQL manager I see only format
yyyy-mm-ddThh:mm:ss. That's a litlle strange and mistaken.

Regard,

Uzytkownik "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> napisal w
wiadomosci news:rhfp91pppbovern3umptsgr3kimkai7ie1@4ax.com...
> On Tue, 31 May 2005 14:31:19 +0200, Zibi wrote:

>
> * 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).
>
Hi,

Thanks all.
I use exctly - SELECT * FROM stat WHERE (data > CONVERT(DATETIME,
'2005-05-24 14:07:28', 102))ORDER BY id Asc and it works. I don't need to
use hours. I found that format in database is yyyy-mm-ddThh:mm:ss.ttt when I
use query analyzer but when I use simple SQL manager I see only format
yyyy-mm-ddThh:mm:ss. That's a litlle strange and mistaken.

Regards,

Zibi

Zibi (zibi@nospam.com) writes:
> I use exctly - SELECT * FROM stat WHERE (data > CONVERT(DATETIME,
> '2005-05-24 14:07:28', 102))ORDER BY id Asc and it works. I don't need
> to use hours. I found that format in database is yyyy-mm-ddThh:mm:ss.ttt
> when I use query analyzer but when I use simple SQL manager I see only
> format yyyy-mm-ddThh:mm:ss. That's a litlle strange and mistaken.

Format in the database is binary. Then it is up to the tool to perform
a textual presentation.

This link may be helpful you:
http://www.karaszi.com/SQLServer/info_datetime.asp.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Uzytkownik "Erland Sommarskog" <esquel@sommarskog.se> napisal w wiadomosci
>
> Format in the database is binary. Then it is up to the tool to perform
> a textual presentation.
>
> This link may be helpful you:
> http://www.karaszi.com/SQLServer/info_datetime.asp.
>
>
Thnks - good site!