Pass date parameter from Access form to SQL Proc 1

[Mirak]

Hi,

I am having major league headache with SQL Server.
I am trying to write a report which will execute a stored procedure based on certain parameters. Two of the parameters in the stored procedure are datetime data types. My main problem is passing the date value from an Access form to the stored procedure.

When I tried converting the field from datetime to varchar or nchar in the stored procedure, the procedure just ignores the parameters and returns all the records from the table.

When I try to use datetime parameters, SQL server does not like the date format and returns an error.

Just anyone know how to "talk some sense" into SQL server when it comes to datetime fields and parameters.

Any assistance will be greatly appreciated.

 

[rsinj]

if you look at the value of the date param in the stored proc, is it coming across correctly?

in the Access query, what mechanism are you using to send the date param? you might try CDate(param) in your query on the Access side to force it to date format assuming the parameter in the SP is declared as Datetime.

 

[Mirak]

Hi,

I tried your suggestion but SQL still had some problems. When I used the Cdate() function to convert my parameter, SQL report a problem with the '/' in the date. However, when I view the data in the actual table (thru Enterprise Manager), the date field date format is dd/mm/yyyy. How can I find out what date format SQL is expecting.

 

[SQLBill]

You usually can't fail if you send the datetime as:

yyyy-mm-dd hh:mm:ss

Or you can use the SQL Server SET DATEFORMAT to let SQL Server know what is being sent to it:

SET DATEFORMAT dmy
SET DATEFORMAT mdy
SET DATEFORMAT ymd

Use the one that matches the format you are using to send the date to SQL Server.

-SQLBill

 

[rsinj]

However, when I view the data in the actual table (thru Enterprise Manager), the date field date format is dd/mm/yyyy."

but, this is not what I was asking - it is what table value/display format is not necessarily what the SP is receiving from the Access front-end.

in your first message, you questioned why the query wasn't restricting by date - you need to see what the value of the date parameter is on the SQL Server side. what I was getting at was in your SP, add a line to save the date parameter somewhere - either to a log table, or write it to the errorlog - so you could see what the SQL Server received.

also, you might just want to make the date parameter a varchar in the SP definition and then deal with whatever conversion is necessary on the SQL Server side - minimally until you can see what the SQL Server is receiving from the client.

SQLBill also offers good info.

 

[SQLBill]

To support what rsinj says (and to further support my own response):

This input 01/02/2004 can mean two different things to SQL Server. It can be January 02, 2004 or it can be 1 February 2004.

Which is it for YOUR SQL Server? You would have to check the collation setting to find out. Or you could try:

DECLARE @mydate DATETIME
SET @mydate = '01/02/2004'
SELECT CONVERT(VARCHAR(10), @mydate, 106)

What does that return? 01 Feb or 02 Jan? Is that what you expected?

If it's not what you expected, you will have to include the proper SET DATEFORMAT.

-SQLBill

 

[Mirak]

Hi,

I finally got it to work. I basically used the SET DATEFORMAT ymd as suggested by SQL Bill. This will tell SQL what date format to expect. That was half of the solution.

The other half, as rsinj pointed out, the date might not be coming across correctly to SQL Server. Using the CDate() built-in function did not work either because it formats the date using the OS system format (dd/mm/yyyy). Therefore, to explicitly force Access to send the date in a format required by SQL Server, I created my own function to format the date:
------------------------------------------------------------
Public Function FormatDate(DateToFormat As String) As String

'This function will be called to convert date to compatible format for SQL Server
On Error GoTo error

Dim yyyy, mm, dd As String

Let yyyy = Year(Date)
Let mm = Month(Date)
Let dd = Day(Date)


DateToFormat = Format(DateToFormat, "yyyy-mm-dd")

FormatDate = CStr(DateToFormat)

Exit Function

error: MsgBox Err.Description, vbOKOnly + vbCritical
MsgBox "This is format " & DateToFormat

End Function
------------------------------------------------------------
If you notice, this function will accept a String parameter and return a String datatype, not a Date as one would expect. Also, the parameters in the stored procedure are datetime formats, because the column in the table stores datetime values.

To pass values to a datetime parameter in a stored procedure, just pass the parameter thru this function first and then to your sp. Remember also to pass the value in double qoutes because it is a string or varchar datatype.

There was also no need to do an explicit conversion to date in the stored procedure on the server side.

Please bear in mind that there are many ways to skin a cat. I was using forms in MS Access. This method is tried and tested.

Big thanx to SQLbill and rsinj.
For members having trouble with SQL/Stored Procedure date formating communication to external application, I am sure this thread can be of vital assistance.

mirak
Remember to Keep on Keeping on...Joe Dirt

 

[rsinj]

Dim yyyy, mm, dd As String

Let yyyy = Year(Date)
Let mm = Month(Date)
Let dd = Day(Date)


you do understand that in your function, the above lines of code do absolutely nothing - right?

in the line:

DateToFormat = Format(DateToFormat, "yyyy-mm-dd")


the "yyyy-mm-dd" does not use the variables yyyy, mm, and dd which you have set to year, month, day. it works correctly only because DateToFormat is implicitly in a date format which Access sees. "yyyy-mm-dd" is the date format string.

anyhow, glad you got it working.

 

[rsinj]

actually, looking closer at your function (in the context that the above code I indicated does nothing) - the whole thing actually does nothing more than the Format statement - you even reset the input string to it.

what I'm saying - you don't need the function at all.

where ever you're calling the function from, instead of

DateToUse = FormatDate(DateToFormat)

you could simply replace it with:

DateToUse = FormatDate(DateToFormat, "yyyy-mm-dd")

have the same result, and eliminate the function entirely.

 

[rsinj]

>
you could simply replace it with:

DateToUse = FormatDate(DateToFormat, "yyyy-mm-dd")
>


that one should read:

DateToUse = Format(DateToFormat, "yyyy-mm-dd")

 

[clevelandjwb]

I am late to the table in this discussion but I would simply like to know how to call a Stored Procedure which is stored in a MS SQL database from Access. Also, if the stored procedure returns data (as in a select statement with no INTO clause) can Access capture that data and place it in a table (or treat it as a query)?

Thanks for any help

John

 

[SQLSister]

Clevelendjwb, Your question belongs in a separate thread on the Access forum. I believe that one of the FAQ in the Access VBA programming forum answers your questions.

 

[clevelandjwb]

Thanks, I will check there.