Executing stored procedures with date parameters: Command Object vs Connection Object

Question

When supplying dates to a stored procedure via a parameter I'm a little confused over which format to use for the dates. My original VBA syntax used the ADO Connection object to execute the stored procedure:

Set SentDetailRS = Me.ADOConnectionToIntegrity.Execute("dbo.s_SelectAggregatedSentDetailList '" & fCSQLDate(EffectiveDate) & "'", , adCmdText)

This works fine for me using the date syntax 'yyyy-mm-dd' but when another user executes the code they recieve the error: 13 'Type Mismatch'. After some experimentation I found that supplying the date in the format 'dd/mm/yyyy' fixes this error for the user but now gives me the error! Executing the stored procedure using a command object with parameters works regardless of the format of the date (I assume ADO is taking care of the formatting behind the scenes). I thought that using the format 'yyyy-mm-dd' would work universally with SQL Server? I'm also perplexed as to why this problem appears to be user specific? I noticed that my default language on SQL Server is 'English' whereas the other user's default language is 'British English', could that cause the problem? I'm using ADO 2.8 with Access 2003 and SQL Server 2000, SQL Server login is via Windows integrated security.

Answer 1

I would guess that fCSQLDate function is culture-specific - i.e. it will parse the date based on the user's locale settings. That's why you see the problem.

Anyway, using queries with concatenated strings is always a bad idea (injection attacks). You are better off if you use parameters.

Answer 2

Access uses # as date field delimiter. The format should be #mm/dd/yyyy# probably the #mm-dd-yyyy# will also work fine.

Answer 3

Be careful, and do not believe that ADO is taking care of the problem. Universal SQL date format is 'YYYYMMDD', while both SQL and ACCESS are influenced by the regional settings of the machine in the way they display dates and convert them in character strings.

Do not forget that Date separator is # in Access, while it is ' in SQL

My best advice will be to systematically convert your Access #MM-DD-YYYY# (or similar) into 'YYYYMMDD' before sending the instruction to your server. You could build a small function such as:

Public function SQLdateFormat(x_date) as string

SQLDateFormat = _
   trim(str(datePart("yyyy",x_date))) & _
   right(str(datePart("m",date)),2) & _
   right(str(datePart("d",date)),2)

   ''be carefull, you might get something like '2008 9 3'
SQLDateFormat = replace(functionSQLDateFormat," ","0")
   '' you will have the expected '20080903'

End function

If you do not programmatically build your INSERT/UPDATE string before sending it to the server, I will then advise you to turn the regional settings of all the machines to the regional settings of the machine hosting SQL. You might also have to check if there is a specific date format on your SQL server (I am not sure). Personnaly, I solved this kind of localisation problems (it also happens when coma is used as a decimal separator in French) or SQL specific characters problems (when quotes or double quotes are in a string) by retreating the SQL instructions before sending them to the server.

Answer 4

Sorry I don't know mysql, but with oracle I would always explicity state the format that I was expecting the format to be in, eg: 'DD-MM-YYYY', to avoid (regional) date format problems

Answer 5

Why not use the format

dd mmm yyyy

There is only one way it can be interpreted.

Answer 6

You can use the Date() function to return a universal date based on the machine date and time settings. The region settings on the machine will determine how it it formatted on the client end. If you leave the field as strictle a DateTime field then the cleint region settings can format the date.

Going into the server, using the Date() function should aslo work (returning a universal date value).

Also, use a command object and parameters in your query when you pass them to avoid SQL injection attacks on string fields.