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,
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/techin.../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/techin.../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!

Dataflow slows dramatically down after passing 1.6 million records ?

If i have a dataflow witch moves records from one OLE DB Source to a OLE DB Destination, the step slow down when getting above passing 1.5 - 1.6 million records.The Source is a SQL 2000 and the Destination is SQL Server 2005. the Package is run from a laptop with 1.5 GB ram

What can i try to help it ?Check the perf counters to see if the package starts swapping when the slowdown occurs. If this is the case then there is not much to be done. You could try adding more memory or making the destination run faster (i.e. drop indicies, faster spindle, etc). Is the destination on the same PC as the package is running? If so then they are competing for memory so you could run the package on a different PC. Also, the next CTP has a minor change that you may find helps with this.

Thanks,|||Start by checking in Task Manager how much memory the package (i.e. dtexec.exe) uses - does it grow all the time?
And when it slows down, are you out of memory + swapping starts + CPU usage goes down?
If this is the case - do you expect memory usage to grow like this, given your data flow? Which component could be gobbling up the memory? Can you change its settings to improve the situation? Or change the data flow to not use the troublesome component?
Or is there a memory leak that creates a problem?
If the bottleneck isn't memory at all, you obviously have to look in other places...

Cheers,
KDog|||

Matt David wrote:

Check the perf counters to see if the package starts swapping when the slowdown occurs.

How do you check the perf counters?
Regards,
Henk|||Use the Windows Performance Monitor (Administrative Tools-->Performance). In there, there is an IS object containing various counters that you can use to monitor performance.

There is a help file provided that explains how to use Windows Performance Monitor.

-Jamie