Help with SQL query from ASP

Hi,
Not sure if I have posted this is the right place, if anyone could
point me in the direction if I'm wrong I'd be grateful.
Anyway, heres my query, I have a SQL query which is searching records
where a date has past, and it works fine in SQL query analyser. Heres
an example of the query...
SELECT
PROD_NO,
B.BRAND,
PRODUCT_NAME,
D.DATE_SENT,
D.DATE_RETURN
FROM dbo.PRODUCTS AS P
INNER JOIN BRANDS AS B
ON P.BRAND_ID = B.BRAND_ID
INNER DATES AS D
ON D.UID = P.UID
WHERE STATUS_ID = '3'
AND DATE_RETURN < '2005-04-25 00:00:00'
This works fine in query analyser, but when I pass the same script to
the server from an ASP page (where I am replacing the date_return value
with an ASP variable representing todays date as above) I get the
following message...
"The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value."
Does anyone have any ideas? Maybe I'm going the wrong way about this?
Thanks
STR8Badly formatted datetime string. I suggest you start by reading relevant sec
tions here:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<straighteight@.gmail.com> wrote in message
news:1114513868.795922.276930@.g14g2000cwa.googlegroups.com...
> Hi,
> Not sure if I have posted this is the right place, if anyone could
> point me in the direction if I'm wrong I'd be grateful.
> Anyway, heres my query, I have a SQL query which is searching records
> where a date has past, and it works fine in SQL query analyser. Heres
> an example of the query...
> SELECT
> PROD_NO,
> B.BRAND,
> PRODUCT_NAME,
> D.DATE_SENT,
> D.DATE_RETURN
> FROM dbo.PRODUCTS AS P
> INNER JOIN BRANDS AS B
> ON P.BRAND_ID = B.BRAND_ID
> INNER DATES AS D
> ON D.UID = P.UID
> WHERE STATUS_ID = '3'
> AND DATE_RETURN < '2005-04-25 00:00:00'
> This works fine in query analyser, but when I pass the same script to
> the server from an ASP page (where I am replacing the date_return value
> with an ASP variable representing todays date as above) I get the
> following message...
> "The conversion of a char data type to a datetime data type resulted in
> an out-of-range datetime value."
> Does anyone have any ideas? Maybe I'm going the wrong way about this?
> Thanks
> STR8
>|||To add to Tibor's response, you can also use a parameterized query so that
you don't need to bother with formatting SQL string literals in your code.
Hope this helps.
Dan Guzman
SQL Server MVP
<straighteight@.gmail.com> wrote in message
news:1114513868.795922.276930@.g14g2000cwa.googlegroups.com...
> Hi,
> Not sure if I have posted this is the right place, if anyone could
> point me in the direction if I'm wrong I'd be grateful.
> Anyway, heres my query, I have a SQL query which is searching records
> where a date has past, and it works fine in SQL query analyser. Heres
> an example of the query...
> SELECT
> PROD_NO,
> B.BRAND,
> PRODUCT_NAME,
> D.DATE_SENT,
> D.DATE_RETURN
> FROM dbo.PRODUCTS AS P
> INNER JOIN BRANDS AS B
> ON P.BRAND_ID = B.BRAND_ID
> INNER DATES AS D
> ON D.UID = P.UID
> WHERE STATUS_ID = '3'
> AND DATE_RETURN < '2005-04-25 00:00:00'
> This works fine in query analyser, but when I pass the same script to
> the server from an ASP page (where I am replacing the date_return value
> with an ASP variable representing todays date as above) I get the
> following message...
> "The conversion of a char data type to a datetime data type resulted in
> an out-of-range datetime value."
> Does anyone have any ideas? Maybe I'm going the wrong way about this?
> Thanks
> STR8
>|||Good point, Dan. I absolutely agree.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eb2f76lSFHA.3664@.TK2MSFTNGP15.phx.gbl...
> To add to Tibor's response, you can also use a parameterized query so that
you don't need to
> bother with formatting SQL string literals in your code.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <straighteight@.gmail.com> wrote in message
> news:1114513868.795922.276930@.g14g2000cwa.googlegroups.com...
>|||Thanks guys, the link gave me a couple of hints which lead to me
sorting it out (ie. drop the dashes and it was fine!) As for the
"parameterized query", just a newbie to SQL here (month or so...) but
will certainly look into it, thanks for the advice! :)
Cheers