Datetime issues when querying SQL Server

TheMSsForum.com: The Microsoft Software Forum

Hi,

I have an IIS query that selects information from a table
within SQL Server 2000. When the function is called, it
gives error message "Microsoft OLE DB Provider for SQL
Server error '80040e07'". A Datetime error.

The line that fails is:

SELECT Name, Description, Reason, StartDate, EndDate,
Forename, Surname FROM V_GetPartnerships WHERE
PartnershipId = 2 AND (StartDate <= 'Wednesday, October
29, 2003' AND EndDate >= 'Wednesday, October 29, 2003')
ORDER BY name;

The datetime field works OK when using correct date
terms, as tested by manually manipulating the date
format, it is only when it using this
extended 'Wednesday, October 29, 2003' that is NOT
recognised by the field.

I have checked the outputs of the date formats using a
simple VB script, and they are as expected (i.e. October
29, 2003).

Where is it getting this extended date from??

This database has been developed over several years and
recently migrated to new servers. This error only appears
on the new system. It DOES NOT affect the old system.
Top

Re: Datetime issues when querying SQL Server by Kristofer

Kristofer
Wed Oct 29 10:50:31 CST 2003

Hello,

As far as i know, SQL Server do not recognize that string as a date, hence
the problem. If you run that in Query Analyzer, you would probably get this
error message:
"Syntax error converting datetime from character string."

If you have Books Online (the documentation for SQL Server), you can see
what strings it will recognize as a date.

Or is this what you asked for?
http://www.adopenstatic.com/faq/systemdateformat.asp

I'm not sure if i understood your question completely, but maybe something
of what i wrote helps you, i hope so.

--
Regards,
Kristofer Gafvert - IIS MVP
http://www.ilopia.com - FAQ & Tutorials for Windows Server 2003, and SQL
Server 2000
Reply to newsgroup only. Remove NEWS if you must reply by email, but please
do not.

Problems with spam and viruses? See
http://www.ilopia.com/security/newsposting.aspx


"Martin Powell" <martin.powell@clifford-house.com> wrote in message
news:0d0101c39e30$55ac2630$a501280a@phx.gbl...
> Hi,
>
> I have an IIS query that selects information from a table
> within SQL Server 2000. When the function is called, it
> gives error message "Microsoft OLE DB Provider for SQL
> Server error '80040e07'". A Datetime error.
>
> The line that fails is:
>
> SELECT Name, Description, Reason, StartDate, EndDate,
> Forename, Surname FROM V_GetPartnerships WHERE
> PartnershipId = 2 AND (StartDate <= 'Wednesday, October
> 29, 2003' AND EndDate >= 'Wednesday, October 29, 2003')
> ORDER BY name;
>
> The datetime field works OK when using correct date
> terms, as tested by manually manipulating the date
> format, it is only when it using this
> extended 'Wednesday, October 29, 2003' that is NOT
> recognised by the field.
>
> I have checked the outputs of the date formats using a
> simple VB script, and they are as expected (i.e. October
> 29, 2003).
>
> Where is it getting this extended date from??
>
> This database has been developed over several years and
> recently migrated to new servers. This error only appears
> on the new system. It DOES NOT affect the old system.


Top

Re: Datetime issues when querying SQL Server by Martin

Martin
Wed Oct 29 11:00:57 CST 2003

That is correct, SQL does not allow that format. I can't
find where this format is being read from. As stated, it
is getting the date and time format from somewhere within
the system, but cannot locate where. I have checked all
Regional Options - could it be coming from IIS??

>-----Original Message-----
>Hello,
>
>As far as i know, SQL Server do not recognize that
string as a date, hence
>the problem. If you run that in Query Analyzer, you
would probably get this
>error message:
>"Syntax error converting datetime from character string."
>
>If you have Books Online (the documentation for SQL
Server), you can see
>what strings it will recognize as a date.
>
>Or is this what you asked for?
>http://www.adopenstatic.com/faq/systemdateformat.asp
>
>I'm not sure if i understood your question completely,
but maybe something
>of what i wrote helps you, i hope so.
>
>--
>Regards,
>Kristofer Gafvert - IIS MVP
>http://www.ilopia.com - FAQ & Tutorials for Windows
Server 2003, and SQL
>Server 2000
>Reply to newsgroup only. Remove NEWS if you must reply
by email, but please
>do not.
>
>Problems with spam and viruses? See
>http://www.ilopia.com/security/newsposting.aspx
>
>
>"Martin Powell" <martin.powell@clifford-house.com> wrote
in message
>news:0d0101c39e30$55ac2630$a501280a@phx.gbl...
>> Hi,
>>
>> I have an IIS query that selects information from a
table
>> within SQL Server 2000. When the function is called, it
>> gives error message "Microsoft OLE DB Provider for SQL
>> Server error '80040e07'". A Datetime error.
>>
>> The line that fails is:
>>
>> SELECT Name, Description, Reason, StartDate, EndDate,
>> Forename, Surname FROM V_GetPartnerships WHERE
>> PartnershipId = 2 AND (StartDate <= 'Wednesday, October
>> 29, 2003' AND EndDate >= 'Wednesday, October 29, 2003')
>> ORDER BY name;
>>
>> The datetime field works OK when using correct date
>> terms, as tested by manually manipulating the date
>> format, it is only when it using this
>> extended 'Wednesday, October 29, 2003' that is NOT
>> recognised by the field.
>>
>> I have checked the outputs of the date formats using a
>> simple VB script, and they are as expected (i.e.
October
>> 29, 2003).
>>
>> Where is it getting this extended date from??
>>
>> This database has been developed over several years and
>> recently migrated to new servers. This error only
appears
>> on the new system. It DOES NOT affect the old system.
>
>
>.
>
Top