date problem

[masiwan]

I want to pass a date value on a microsoft access form to an SQL server data base, as a parameter, but I get the data conversion error: cannot convert var char data to smallTime,something like this. The form is capturing data and I have set data format for the field accepting the date value to Small date what can I do to resolve these date conversion problems?

 

[ChopinFan]

I've had the same kind of issue. Dates are passed by access as strings. You must pass it as a string and your sproc can convert it back to a date value for the table.

 

[masiwan]

give me a clue how will I do the conversion, these CONVERT, CAST functions I have tried dont seem to be giving me the right thing I want. Possibly I'm missing out sometghing. I want the British format of date ie: dd/mm/yyyy and SQL server seems to be stack to American format m/d/yy
Please someone help

 

[ChopinFan]

From your comment it sounds like you already know how to pass the parameter to the sproc in VBA, so I'll pick it up from there.
This is how I'm doing it in a sproc that inserts a new record in a table. You can modify this for your needs. In the sproc, do this:

1. In the list of input parameters, add a variable for the date value as a varchar:
@DateVariable varchar (10)

2. Declare a new variable of data type datetime:
Declare @NewDateVariable Datetime

3. Include a little bit of validation:
if @DateVariable = ''
set @NewDateVariable = Null
Else
Begin
Set @NewDateVariable = convert(datetime, @DateVariable,101) ;
End

4. In your insert statement, indicate the @NewDateVariable to save in the table.

As for British time format, it should be a settings issue in your database or even on the entire server that needs to be changed from the standard settings. I'm afraid I can't help too much there.

 

[danvlas]

Well, if you format the date parameter in the client application as:

Format(YourDate,"mm/dd/yyyy")

that should do the trick.

Formating as Short Date uses the regional settings and confuses SQL Server.

For instance, passing 14/02/2006 will result in an error.

HTH




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html