Problem with dates in select statement

[cr84net2]

I am used to working in access and have a project where I am using sql server. I am trying to find dates in a field between two other dates. Here is one of many variations I have tryed.

tourndis= Now()
tournreg= Now() + 6

sqlstr="Select tournname, tourndat from tournaments WHERE tourndat BETWEEN " & tourndis & " AND " & tournreg

When I response.write the sqlstr things look ok. Other variations have caused errors and some have appeared to have done nothing.

tourndat is a smalldatetime field with a date like 8/16/2008

Any help would be greatly appreciated, my mind is numb at this point.

 

[johnwm]

Delimiter for dates is ' (single quote mark/apostrophe) for MSSQL. You don't appear to be using any delimiter at present.

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[Spork52]

You can't just add 6 to NOW(). In both VBScript and T-SQL you have to use DATEADD functions. Google a bit. Look for T-SQL functions DATEADD, DATEPART, GETDATE() You can construct the date stuff using T-SQL instead of VBScript.

 

[wvdba]

i think the delimiter for ms-sql is #, like: #08/15/2008#

 

[wvdba]

this syntax:
tourndis= Now()
tournreg= Now() + 6
works in vbscript. i tested it. the result is:
8/21/2008 9:12:50 AM

 

[gmmastros]

i think the delimiter for ms-sql is #, like: #08/15/2008#

No.

The date delimiter for Microsoft Access is #
The date delimiter for Microsoft SQL Server is '

(just like johnwm said in the first response to this question.)



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom