Passing date parameters to SQL server 1

[vbajock]

This is probably a pretty simple question, but I can't seem to find the answer to it anywhere on the web. When using an OLEDB connection to SQL


strconn= "Provider=SQLOLEDB;Server=SQL;Database=SomeDB;User Id=XXX;Password=XXXXX;"
cSql.Open strconn

the connection returns errors when I pass it a SQL query using the Access syntax for a date parameter like this:

sql="SELECT tblSchedule.*
sql=sql+" FROM tblSchedule
sql=sql+" WHERE (((tblSchedule.Date)=#"+sDate+"#));

If I use a linked table and pass it the same query via the currentproject.connection connection, it works fine, but SQL server errors out on it. Can anyone tell me the correct SQL syntax for this type of connection to pass a date to a SQL table?

Thanks
Kirk

 

[RoyVidar]

With single quotes

[tt]...tblSchedule.Date='" & sDate & "'"[/tt]

properly formatted, for instance ISO 8601 - "yyyy-mm-dd"

Roy-Vidar

 

[vbajock]

So, even tho SQL has a datetime data type, it just handles it like any string?

 

[RoyVidar]

I wouldn't read the single quotes as that. I'm more inclined to see them as denoting the starting and ending position of literal values of both date/time and text type within a dynamic SQL string, which is sent to the DB engine for evaluation, decoding and execution.

After the DB engine has evaluated and decoded (if that's how the process goes), then I'm sure SQL server handles the date literal as date.

Roy-Vidar

 

[vbajock]

Thanks Roy, for your usual in depth discussion, you are a major asset to this forum.

Kirk