Sending SQL Str by ADODB - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Sending SQL Str by ADODB

Author

Topic

agniwoni
Starting Member

28 Posts

Posted - 2007-06-26 : 04:28:07

Hi, i'm trying to retrieve some rows using a proper sql query:
"SELECT * FROM v_report1 WHERE [date] >= CONVERT(DATETIME, '2007-06-02') and [date] <= CONVERT(DATETIME, '2007-06-25')"

Mentioned query is ok by the query analizer, but when it comes to pushing it by ADODB's Recordset.Open method (Visual Basic for Applications, Access'2003) - it fails returning an error:
"Run-time error '-2147217913 (80040e07)':
The conversion of char data type to a datetime data type resulted in an out-of-range datetime value.".

I tried also implicit, explicit, Cast, Convert convertion... and it didn't succeeded at all. Query analizer returns proper recordset, and the recordset.open method returns that stupid error.

So I ask: WTF

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-06-26 : 05:03:40

Is column [date] in v_report1 datatype char?

Try doing your query like this:
"SELECT * FROM v_report1 WHERE [date] >= CONVERT(DATETIME, '20070602') and [date] <= CONVERT(DATETIME, '20070625')"

CODO ERGO SUM

agniwoni
Starting Member

28 Posts

Posted - 2007-06-26 : 05:32:06

quote:
Originally posted by Michael Valentine Jones

Is column [date] in v_report1 datatype char?

Try doing your query like this:
"SELECT * FROM v_report1 WHERE [date] >= CONVERT(DATETIME, '20070602') and [date] <= CONVERT(DATETIME, '20070625')"

CODO ERGO SUM

Thanks for Your help

So i did... but the result is quite suprising: query doesn't return an error, but also doesn't return any rows (as it should).
Any ideas? Regards!

agniwoni
Starting Member

28 Posts

Posted - 2007-06-26 : 05:33:23

quote:
Originally posted by Michael Valentine Jones

Is column [date] in v_report1 datatype char?

Try doing your query like this:
"SELECT * FROM v_report1 WHERE [date] >= CONVERT(DATETIME, '20070602') and [date] <= CONVERT(DATETIME, '20070625')"

CODO ERGO SUM

I forgot to reply with this one:
Column [date] is a Datetime type.

agniwoni
Starting Member

28 Posts

Posted - 2007-06-26 : 05:52:15

quote:
Originally posted by Michael Valentine Jones

Is column [date] in v_report1 datatype char?

Try doing your query like this:
"SELECT * FROM v_report1 WHERE [date] >= CONVERT(DATETIME, '20070602') and [date] <= CONVERT(DATETIME, '20070625')"

CODO ERGO SUM

Sorry, my user form changed datetime contitions... that's why it went wrong.
NOW IT WORKS!
Thank You for Your time!
Kind regards!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources