datatime error

[Zibi]

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,

[Chandra]

Re: datatime error


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 ***

[Hugo Kornelis]

Re: datatime error

On Tue, 31 May 2005 14:31:19 +0200, Zibi wrote:
[color=blue]
>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?[/color]

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)

[Hugo Kornelis]

Re: datatime error

On Tue, 31 May 2005 15:01:55 GMT, Chandra wrote:
[color=blue]
>
>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[/color]

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)

[Erland Sommarskog]

Re: datatime error

Hugo Kornelis (hugo@pe_NO_rFa ct.in_SPAM_fo) writes:[color=blue]
> 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.[/color]

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@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Zibi]

Re: datatime error


Uzytkownik "Hugo Kornelis" <hugo@pe_NO_rFa ct.in_SPAM_fo> napisal w
wiadomosci news:rhfp91pppb overn3umptsgr3k imkai7ie1@4ax.c om...[color=blue]
> On Tue, 31 May 2005 14:31:19 +0200, Zibi wrote:[/color]
[color=blue]
>
> * 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).
>[/color]
Hi,

Thanks all.
I use exctly - SELECT COUNT(id) AS [stat_ile] FROM stat WHERE (data >
CONVERT(DATETIM E, '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,

[Zibi]

Re: datatime error


Uzytkownik "Hugo Kornelis" <hugo@pe_NO_rFa ct.in_SPAM_fo> napisal w
wiadomosci news:rhfp91pppb overn3umptsgr3k imkai7ie1@4ax.c om...[color=blue]
> On Tue, 31 May 2005 14:31:19 +0200, Zibi wrote:[/color]
[color=blue]
>
> * 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).
>[/color]
Hi,

Thanks all.
I use exctly - SELECT * FROM stat WHERE (data > CONVERT(DATETIM E,
'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

[Erland Sommarskog]

Re: datatime error

Zibi (zibi@nospam.co m) writes:[color=blue]
> I use exctly - SELECT * FROM stat WHERE (data > CONVERT(DATETIM E,
> '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.[/color]

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@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Zibi]

Re: datatime error


Uzytkownik "Erland Sommarskog" <esquel@sommars kog.se> napisal w wiadomosci[color=blue]
>
> 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.
>
>[/color]
Thnks - good site!