What is wrong with this query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

What is wrong with this query?

Author

Topic

rtr1900
Starting Member

48 Posts

Posted - 2008-05-21 : 07:49:20

Hi, I just want to receive the rows where the difference between the date of a column is smaller than 60 days.
If I execute the query using ASP (with a sql query) it works, but executing it using VBS, it tells me that it need a parameter.

I tried

sql="SELECT * FROM TABLE where datediff(DAY,FEC_STOCK, NOW() ) < 60"

and also

sql="SELECT *
FROM TABLE
WHERE (DATEDIFF(DAY, FEC_STOCK, { fn NOW() }) < 27)"

Any idea is welcome!

Thx already

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-21 : 07:55:18

sql = "SELECT * FROM TABLE where datediff(DAY, FEC_STOCK, '" & NOW() & "') < 60"

sql = "SELECT * FROM TABLE WHERE (DATEDIFF(DAY, FEC_STOCK, GETDATE()) < 27)"

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-21 : 07:57:50

To utilize any present index over FEC_STOCK column, you should use

sql = "SELECT * FROM TABLE where FEC_STOCK >= dateadd(DAY, -60, getdate())"
sql = "SELECT * FROM TABLE where FEC_STOCK >= dateadd(DAY, -27, getdate())"

E 12°55'05.25"
N 56°04'39.16"

rtr1900
Starting Member

48 Posts

Posted - 2008-05-21 : 08:04:04

quote:
Originally posted by Peso

sql = "SELECT * FROM TABLE where datediff(DAY, FEC_STOCK, '" & NOW() & "') < 60"

sql = "SELECT * FROM TABLE WHERE (DATEDIFF(DAY, FEC_STOCK, GETDATE()) < 27)"

E 12°55'05.25"
N 56°04'39.16"

Hi Peso,

thx for the fast response. I tried the first line, but it tells me that there are "parameters missing, expected 1".
I checked the syntax, and I believe everything is there.

Strange when I put somethinmg like
sql="SELECT * FROM TABLE where fec_stock > cdate('21/03/2008')"

it works fine

rtr1900
Starting Member

48 Posts

Posted - 2008-05-21 : 08:55:15

Hi Peso,

I solved it using this:

dag=day(now())
maand=month(now())
jaar=year(now())
dat_vol=""&dag&"/"&maand&"/"&jaar&""
tt=dateadd("d", -20, dat_vol)
sql = "SELECT * FROM table where FEC_STOCK >= cdate('"&tt&"')"

And it gives me the correct amount of rows back. Maybe it is not the best way, but it works fine.

Again, i want to thank you for answering my post and this way helping me in the right direction!

thx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources