Date-Formats for SQL-Server

[Tiger955]

Hi!

Please help me to understand this scenario:

For sending a pass through query from Access to SQL-server I have a function to "convert" Access date

Function DateTimeForSQL(dteDate) As String
DateTimeForSQL = "'" & Format(CDate(dteDate), "yyyy-dd-mm h:nn:ss") & "'"
End Function

the result is for example: '2014-31-03 0:00:00'

When I send "EXEC spMyQry '2014-31-03 0:00:00'" to the server it returns the correct data.

In the SP on the server I tried both types of variable declaration:
@MyDate nvarchar(21) and in another trial
@MyDate datetime

When I try to execute
"EXEC spMyQry '2014-31-03 0:00:00'" direct on the server I get an error
"The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value." when @MyDate nvarchar(21)

or
"Error converting data type varchar to datetime." with
@MyDate datetime

But BOTH definitions work when sending "EXEC spMyQry '2014-31-03 0:00:00'" from Access.

But I get an error in Access when I modify my function to
DateTimeForSQL = "'" & Format(CDate(dteDate), "yyyy-mm-dd h:nn:ss") & "'"
although this gives '2014-03-31 0:00:00'

Any idea, why a sql-string from Access cannot be taken 1:1 into SQL-server?

Thanks
Michael

 

[Galaxiom]

The quotes are delimiters in an SQL string.

Normally a parameter is not passed as part of the string but by the parameters collection of the query. I have never tried it so I am not even sure a passthrough can pass a parameter but you could try appending the datetime value directly to the parameters collection of the query.

If I want to run a Stored Procedure I use an ADODB Command object and return a recordset or scalar value as appropriate.

BTW I would refrain from naming your stored procedures with the sp prefix because SQL Server already uses sp for System Procedures.

 

[Tiger955]

Hi Galaxiom!

Thanks your reply. I will try it.

FYI a system procedure starts allways with "sp_" and in literature it is recommended to start user defined sp's with "sp" without unterline to distinguish between functions and stored procedures.
BTW I use DAO in my project (Access2010), can I mix up with ADO?

Michael

 

[Galaxiom]

FYI a system procedure starts allways with "sp_" and in literature it is recommended to start user defined sp's with "sp" without unterline to distinguish between functions and stored procedures.

Ah yes. Only half my brain still firing tonight.

BTW I use DAO in my project (Access2010), can I mix up with ADO?

Yes. An ADO recordset can be used for a form. But it is different enough to require a non-trivial learning curve.

My main use for them is with parameterised stored procedures that return records for a subform dynamically loaded as the main form current record changed. ODBC Linked tables for subforms are very slow by comparison.

 

[Tiger955]

Hi Galaxiom!

Thanks your reply, it would be great if you could let me have some code examples how your solution with the recordsource for a subform works, as I have indeed some performance problems with subforms.

Thanks a lot.
Michael