467,084 Members | 1,190 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,084 developers. It's quick & easy.

datatime error

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,




Jul 23 '05 #1
  • viewed: 2535
Share:
8 Replies

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 ***
Jul 23 '05 #2
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)
Jul 23 '05 #3
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)
Jul 23 '05 #4
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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #5

Uzytkownik "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> napisal w
wiadomosci news:rh********************************@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,
Jul 23 '05 #6

Uzytkownik "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> napisal w
wiadomosci news:rh********************************@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
Jul 23 '05 #7
Zibi (zi**@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #8

Uzytkownik "Erland Sommarskog" <es****@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!
Jul 23 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Tony Wright | last post: by
1 post views Thread by Aravind | last post: by
3 posts views Thread by Sun | last post: by
7 posts views Thread by p | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.