datediff and SQL error

[ianjenn]

Can anyone see the error in the following SQL statement -

&quot;WHERE times.timein Between #&quot; & FormatDateTime(datdays,1) & &quot;# AND #&quot; & FormatDateTime(datend,1) & &quot;# AND times.tech Is Not Null AND times.pharm Is Not Null AND DateDiff(&quot;d&quot;,times.timein,times.timefin)<1;&quot;

The error lies in the datediff part saying expected end of statement. However the same statement works fine in ACCESS. Is there something else I need to do for ASP
please help
thanks

 

[link9]

SQL Server uses (') to surround dates, instead of Access's (#).

See if that clears it up for you.


paul

 

[ianjenn]

okay problem is with dateDiff(&quot;d&quot;...
should be Datediff('d'...

 

[link9]

I'm sorry. I misunderstood your question.

For some reason, I had the idea in my head that you were trying to go to SQL Server w/ this query. (??)

Your problem is that you need to replace:

(&quot;d&quot;,times.timein,times.timefin)<1;&quot;

with:

(&quot;&quot;d&quot;&quot;,times.timein,times.timefin)<1;&quot;

vbScript is seeing that single double quote as the &quot;expected end&quot; of your string, and so it's throwing the error. To get a single double quote into your statement, put double doubles, as I've shown there.


paul

 

[ianjenn]

No I tried that, it doesn't work. The single quote does however work e.g.

DateDiff('d'....
thanks though