Datetime Issues

Hi,
I have an IIS query (using VB) 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;
I have run this same line successfully by omitting
the "Wednesday, " section from the date fields, because
it does not fit in with the datetime defaults.
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'Assuming Startdate and Enddate are DATETIME or SMALLDATETIME columns, try
this:
SELECT [name], [description], reason, startdate, enddate,
forename, surname
FROM V_GetPartnerships
WHERE partnershipid = 2
AND '20031029' BETWEEN startdate AND enddate
ORDER BY name
Always specify dates in the ISO format ('YYYYMMDD') which works
independently of any regional settings.
--
David Portas
--
Please reply only to the newsgroup
--|||This is an SQL database and IIS that previously worked
OK, but has been migrated to new servers.
The date and time selections it is using determines the
query (by the use of VB Combo boxes within the website)
and any changes to this would require extensive re-
writing of the VB codes.
The datetime field works OK when using correct date
terms, as previously tested, it is only when it using
this extended 'Wednesday, October 29, 2003' that is NOT
recognised by the field.
Any ideas where it would be getting this time field from?
I.E. IIS or SQL Server properties etc.
Or is it possible to update SQL to allow for the day,
month date, year datetime fields.
>--Original Message--
>Assuming Startdate and Enddate are DATETIME or
SMALLDATETIME columns, try
>this:
>SELECT [name], [description], reason, startdate, enddate,
> forename, surname
> FROM V_GetPartnerships
> WHERE partnershipid = 2
> AND '20031029' BETWEEN startdate AND enddate
> ORDER BY name
>Always specify dates in the ISO format ('YYYYMMDD')
which works
>independently of any regional settings.
>--
>David Portas
>--
>Please reply only to the newsgroup
>--
>
>.
>|||SQL Server does not understand below "extended" format. Take the time to do it right, use the format
recommended by David, and you will not run into all kind of problems later. Or read about the
supported formats in Books Online:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_03_765d.asp?frame=true
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Martin Powell" <anonymous@.discussions.microsoft.com> wrote in message
news:0c4b01c39e29$26996260$a501280a@.phx.gbl...
> This is an SQL database and IIS that previously worked
> OK, but has been migrated to new servers.
> The date and time selections it is using determines the
> query (by the use of VB Combo boxes within the website)
> and any changes to this would require extensive re-
> writing of the VB codes.
> The datetime field works OK when using correct date
> terms, as previously tested, it is only when it using
> this extended 'Wednesday, October 29, 2003' that is NOT
> recognised by the field.
> Any ideas where it would be getting this time field from?
> I.E. IIS or SQL Server properties etc.
> Or is it possible to update SQL to allow for the day,
> month date, year datetime fields.
> >--Original Message--
> >Assuming Startdate and Enddate are DATETIME or
> SMALLDATETIME columns, try
> >this:
> >
> >SELECT [name], [description], reason, startdate, enddate,
> > forename, surname
> > FROM V_GetPartnerships
> > WHERE partnershipid = 2
> > AND '20031029' BETWEEN startdate AND enddate
> > ORDER BY name
> >
> >Always specify dates in the ISO format ('YYYYMMDD')
> which works
> >independently of any regional settings.
> >
> >--
> >David Portas
> >--
> >Please reply only to the newsgroup
> >--
> >
> >
> >.
> >|||I am aware that SQL does not support the format that is
in use, the issue is that it is getting this format from
somewhere within the system.
This database has been developed over several years and
recently migrated to new servers and this error has only
appears on the new system. It DOES NOT affect the old
system.
Any ideas where it would be getting this time field from?
>--Original Message--
>SQL Server does not understand below "extended" format.
Take the time to do it right, use the format
>recommended by David, and you will not run into all kind
of problems later. Or read about the
>supported formats in Books Online:
>http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/acdata/ac_8_con_03_765d.asp?frame=true
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Martin Powell" <anonymous@.discussions.microsoft.com>
wrote in message
>news:0c4b01c39e29$26996260$a501280a@.phx.gbl...
>> This is an SQL database and IIS that previously worked
>> OK, but has been migrated to new servers.
>> The date and time selections it is using determines the
>> query (by the use of VB Combo boxes within the website)
>> and any changes to this would require extensive re-
>> writing of the VB codes.
>> The datetime field works OK when using correct date
>> terms, as previously tested, it is only when it using
>> this extended 'Wednesday, October 29, 2003' that is NOT
>> recognised by the field.
>> Any ideas where it would be getting this time field
from?
>> I.E. IIS or SQL Server properties etc.
>> Or is it possible to update SQL to allow for the day,
>> month date, year datetime fields.
>> >--Original Message--
>> >Assuming Startdate and Enddate are DATETIME or
>> SMALLDATETIME columns, try
>> >this:
>> >
>> >SELECT [name], [description], reason, startdate,
enddate,
>> > forename, surname
>> > FROM V_GetPartnerships
>> > WHERE partnershipid = 2
>> > AND '20031029' BETWEEN startdate AND enddate
>> > ORDER BY name
>> >
>> >Always specify dates in the ISO format ('YYYYMMDD')
>> which works
>> >independently of any regional settings.
>> >
>> >--
>> >David Portas
>> >--
>> >Please reply only to the newsgroup
>> >--
>> >
>> >
>> >.
>> >
>
>.
>|||Ahh, I see. I'm no ASP expert, but this is where you have to look (in the application code).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
<anonymous@.discussions.microsoft.com> wrote in message
news:062801c39e2c$1c858d50$a001280a@.phx.gbl...
> I am aware that SQL does not support the format that is
> in use, the issue is that it is getting this format from
> somewhere within the system.
> This database has been developed over several years and
> recently migrated to new servers and this error has only
> appears on the new system. It DOES NOT affect the old
> system.
> Any ideas where it would be getting this time field from?
> >--Original Message--
> >SQL Server does not understand below "extended" format.
> Take the time to do it right, use the format
> >recommended by David, and you will not run into all kind
> of problems later. Or read about the
> >supported formats in Books Online:
> >http://msdn.microsoft.com/library/default.asp?
> url=/library/en-us/acdata/ac_8_con_03_765d.asp?frame=true
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at: http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"Martin Powell" <anonymous@.discussions.microsoft.com>
> wrote in message
> >news:0c4b01c39e29$26996260$a501280a@.phx.gbl...
> >> This is an SQL database and IIS that previously worked
> >> OK, but has been migrated to new servers.
> >>
> >> The date and time selections it is using determines the
> >> query (by the use of VB Combo boxes within the website)
> >> and any changes to this would require extensive re-
> >> writing of the VB codes.
> >>
> >> The datetime field works OK when using correct date
> >> terms, as previously tested, it is only when it using
> >> this extended 'Wednesday, October 29, 2003' that is NOT
> >> recognised by the field.
> >>
> >> Any ideas where it would be getting this time field
> from?
> >> I.E. IIS or SQL Server properties etc.
> >>
> >> Or is it possible to update SQL to allow for the day,
> >> month date, year datetime fields.
> >>
> >> >--Original Message--
> >> >Assuming Startdate and Enddate are DATETIME or
> >> SMALLDATETIME columns, try
> >> >this:
> >> >
> >> >SELECT [name], [description], reason, startdate,
> enddate,
> >> > forename, surname
> >> > FROM V_GetPartnerships
> >> > WHERE partnershipid = 2
> >> > AND '20031029' BETWEEN startdate AND enddate
> >> > ORDER BY name
> >> >
> >> >Always specify dates in the ISO format ('YYYYMMDD')
> >> which works
> >> >independently of any regional settings.
> >> >
> >> >--
> >> >David Portas
> >> >--
> >> >Please reply only to the newsgroup
> >> >--
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||If you are asking why IIS or VB is formatting the date this way then I
really don't know. Presumably it's defined by a regional setting on the
server - it looks like a Windows "Long" date format. This isn't a SQLServer
question so you'll probably get more help posting to an IIS group.
--
David Portas
--
Please reply only to the newsgroup
--|||Thanks Guys,
Will try with IIS, now SQL has been ruled out!
>--Original Message--
>If you are asking why IIS or VB is formatting the date
this way then I
>really don't know. Presumably it's defined by a regional
setting on the
>server - it looks like a Windows "Long" date format.
This isn't a SQLServer
>question so you'll probably get more help posting to an
IIS group.
>--
>David Portas
>--
>Please reply only to the newsgroup
>--
>
>.
>