ASP & SQL date issues

[Jamesy]

Hi,
I've got the age old problem of getting a SP in a MSSQL database to see the
string I'm passing to it as a datetime... I'm getting the ol' error
converting to datetime from varchar problem.

I can't see where the issue is:

The string I'm passing looks like:

'24/01/2005 00:00:00'
'23/02/2005 23:59:59'

All with it's quotes as required..

THe SP is as follows:

SELECT dbo.OrderDetails_Retail.ProductID,
SUM(dbo.OrderDetails_Retail.Quantity) AS RetQtyTotal,
SUM(dbo.OrderDetails_Retail.UnitPrice) AS RetPriceTotal,
dbo.Products.ReportColour, dbo.Products.ProductBrand +
' - ' + dbo.Products.ProductName AS ProductName
FROM dbo.Orders_Retail INNER JOIN
dbo.OrderDetails_Retail ON dbo.Orders_Retail.OrderID =
dbo.OrderDetails_Retail.OrderID INNER JOIN
dbo.Products ON dbo.OrderDetails_Retail.ProductID =
dbo.Products.ProductID
WHERE (dbo.Orders_Retail.PayStatus <> 'Pending') AND
(dbo.Orders_Retail.OrderStatus <> N'Deleted') AND
(dbo.Orders_Retail.OrderDate BETWEEN
@StartDate AND @ThisDate) AND
(dbo.Orders_Retail.account = @AccountSession)
GROUP BY dbo.OrderDetails_Retail.ProductID, dbo.Products.ReportColour,
dbo.Products.TradeOrder, dbo.Products.ProductName,
dbo.Products.ProductBrand
ORDER BY dbo.Products.TradeOrder

I've tried every different format I can think of but nothing.. I've also
tried to use the CONVERT and.or CAST functions from within the SQL but
nothing..

If I run the SP through access project and paste those exact strings in, it
works no problem... just when ran through the ASP page it breaks.

Any ideas?

James

[Julian Roberts]

Pass dates in ISO format of YYYY-MM-DD HH:MM

--
Jules
[url]http://www.charon.co.uk/charoncart[/url]
Charon Cart 3
Shopping Cart Extension for Dreamweaver MX/MX 2004

[Jon Spivey]

Hi,
Julian's answer is best but if you don't want to change your code you can
put as the first line of your SP
set dateformat dmy

this will let you enter dates in d/m/y format.

Jon

"Jamesy" <NOSPAM@patchworkmedia.co.uk> wrote in message
news:cvi696$jg6$1@forums.macromedia.com...

> Hi,
> I've got the age old problem of getting a SP in a MSSQL database to see
> the string I'm passing to it as a datetime... I'm getting the ol' error
> converting to datetime from varchar problem.
>
> I can't see where the issue is:
>
> The string I'm passing looks like:
>
> '24/01/2005 00:00:00'
> '23/02/2005 23:59:59'
>
> All with it's quotes as required..
>
> THe SP is as follows:
>
> SELECT dbo.OrderDetails_Retail.ProductID,
> SUM(dbo.OrderDetails_Retail.Quantity) AS RetQtyTotal,
> SUM(dbo.OrderDetails_Retail.UnitPrice) AS RetPriceTotal,
> dbo.Products.ReportColour, dbo.Products.ProductBrand
> + ' - ' + dbo.Products.ProductName AS ProductName
> FROM dbo.Orders_Retail INNER JOIN
> dbo.OrderDetails_Retail ON dbo.Orders_Retail.OrderID
> = dbo.OrderDetails_Retail.OrderID INNER JOIN
> dbo.Products ON dbo.OrderDetails_Retail.ProductID =
> dbo.Products.ProductID
> WHERE (dbo.Orders_Retail.PayStatus <> 'Pending') AND
> (dbo.Orders_Retail.OrderStatus <> N'Deleted') AND
> (dbo.Orders_Retail.OrderDate BETWEEN
> @StartDate AND @ThisDate) AND
> (dbo.Orders_Retail.account = @AccountSession)
> GROUP BY dbo.OrderDetails_Retail.ProductID, dbo.Products.ReportColour,
> dbo.Products.TradeOrder, dbo.Products.ProductName,
> dbo.Products.ProductBrand
> ORDER BY dbo.Products.TradeOrder
>
> I've tried every different format I can think of but nothing.. I've also
> tried to use the CONVERT and.or CAST functions from within the SQL but
> nothing..
>
> If I run the SP through access project and paste those exact strings in,
> it works no problem... just when ran through the ASP page it breaks.
>
> Any ideas?
>
> James
>
>
>
>