"Des Crocker" <des_crocker@harcourt.com> wrote in message
news:353e39d.0309230614.64417686@posting.google.com...
> Dear all,
>
> I have an asp web application that submits SQL commands to our SQL 7
> database. When trying to run one of the users queries with a date
> selection where the days > 12 I get the following error:
>
> [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a
> char data type to a datetime data type resulted in an out-of-range
> datetime value.
>
> We always store all our dates as yyyymmdd and this has always seemed
> quite unambiguous. Strange thing is that it runs fine in QA, using a
> date format of 'yyyy-mm-dd'. But when it tries to run through the asp
> application on IIS using a system DSN ODBC to the SQL box it fails.
>
> The select statement is this:
>
> select distinct 'ADES0102XD', cci.customeraccount, cci.companyname ,
> cci.fullname, cci.address1, cci.address2, cci.street, cci.address3,
> cci.town, cci.county, cci.country, cci.postcode from customersales as
> cs inner join customercontactinfo as cci on cs.customeraccount =
> cci.customeraccount WHERE ((CS.OrderType = 3 OR CS.OrderType = 4) OR
> CS.CustomerAccount LIKE 'PR%') AND (cs.hitdate >= '2003-1-31' and
> cs.hitdate <= '2003-2-25')
>
> Why is it failing when coming through the IIS/DSN ODBC route, but
> works ok if I run it myself in QA? I have tried adding 'set dateformat
> ymd' into my asp code but to no avail. Are there some date format
> settings on the IIS box that I don't know about? I've tried changing
> the default language on the login account between British English and
> English, but this has not solved the problem. The client, IIS and
> server box are all set to English (United Kingdom) region settings.
>
> Cheers,
>
> Des

"Ryan Waight" <Ryan_Waight@nospam.hotmail.com> wrote in message
news:eIDvJ4dgDHA.2248@TK2MSFTNGP09.phx.gbl...
> ODBC must be doing a conversion on the date, try running a Profiler Trace
to
> see what command is actually asked of the SQL Server. You could also try
> passing the dates in a Long Date format so there is no ambiguity. eg '31
Jan
> 2003'
>
> --
> HTH
> Ryan Waight, MCDBA, MCSE
>
> "Des Crocker" <des_crocker@harcourt.com> wrote in message
> news:353e39d.0309230614.64417686@posting.google.com...
> > Dear all,
> >
> > I have an asp web application that submits SQL commands to our SQL 7
> > database. When trying to run one of the users queries with a date
> > selection where the days > 12 I get the following error:
> >
> > [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a
> > char data type to a datetime data type resulted in an out-of-range
> > datetime value.
> >
> > We always store all our dates as yyyymmdd and this has always seemed
> > quite unambiguous. Strange thing is that it runs fine in QA, using a
> > date format of 'yyyy-mm-dd'. But when it tries to run through the asp
> > application on IIS using a system DSN ODBC to the SQL box it fails.
> >
> > The select statement is this:
> >
> > select distinct 'ADES0102XD', cci.customeraccount, cci.companyname ,
> > cci.fullname, cci.address1, cci.address2, cci.street, cci.address3,
> > cci.town, cci.county, cci.country, cci.postcode from customersales as
> > cs inner join customercontactinfo as cci on cs.customeraccount =
> > cci.customeraccount WHERE ((CS.OrderType = 3 OR CS.OrderType = 4) OR
> > CS.CustomerAccount LIKE 'PR%') AND (cs.hitdate >= '2003-1-31' and
> > cs.hitdate <= '2003-2-25')
> >
> > Why is it failing when coming through the IIS/DSN ODBC route, but
> > works ok if I run it myself in QA? I have tried adding 'set dateformat
> > ymd' into my asp code but to no avail. Are there some date format
> > settings on the IIS box that I don't know about? I've tried changing
> > the default language on the login account between British English and
> > English, but this has not solved the problem. The client, IIS and
> > server box are all set to English (United Kingdom) region settings.
> >
> > Cheers,
> >
> > Des
>
>