September 1, 2005 at 3:50 am
Hi Guys,
Have a really annoying problem which just won't go away.
I'm passing a date value from a text box on a form to a report which then in turn passes it to a sp to generate the reports contents. The reports input parameter property is: forms!frmrptPartStats!txtDateFrom. When I pass in 1/1/1994 everything works ok but when I try 31/12/1994 it returns 'Error converting datatype nvarchar to datetime. Running the sp separately works ok regardless of what format the date is entered ie mm/dd/yyyy or dd/mm/yyyy or yyyy/mm/dd. However running the report to call the sp also returns this error.
My regional settings are English(Australia), if I change them to English(US) the problem goes away, is anyone aware of any issues Access has converting nvarchar to datetime when used as a report parameter, it obviously getting mixed up somewhere with the dd/mm or mm/dd side of things. Is there a way of passing the SP parameters without having access convert them to text?
Sorry to fill up the forum with date issues but this one just won't go away.
September 1, 2005 at 6:09 am
If you are always passing MM/DD/YYYY then you could (at the top of the SP) SET DATEFORMAT MDY and see if that helps...
Good Hunting!
AJ Ahrens
webmaster@kritter.net
September 1, 2005 at 7:39 am
If the interface to a T-SQL procedure is from an external source, i.e., not from another T-SQL procedure that I have under my control, I have found that it saves time in the long run to define most, if not quite all, input parameters as 'varchar' and do the conversions myself within the called T-SQL procedure. It's not that I don't trust other interfaces... well, okay, it is exactly that! 🙂
September 1, 2005 at 12:48 pm
Thx for all the help on this one over multiple threads, solved by adding 'set dateformat dmy' to sp and passing date values into sp as varchar(10) then performing a convert(datetime, @variable).
Wacky part is that none of the above is required if your regional settings are set to English(US), anything else though and your screwed!
September 2, 2005 at 1:56 am
I had a similar prolem set up as English (UK)
I got round it by creating function in Access to convert the date into a text string of the right format for SQL
Then pass the text string to the SP via ADO as text not date , SQL interprest it fine
This is it
Cheers
Mike
Public Function SQLDate(DateEntry As Date) As String
Dim DayNo As Integer
Dim MonthNo As Integer
Dim YearNo As Integer
DayNo = DatePart("D", DateEntry)
MonthNo = DatePart("m", DateEntry)
YearNo = DatePart("yyyy", DateEntry)
SQLDate = MonthNo & "/" & DayNo & "/" & YearNo
SQLDate = SQLDate & " " & TimeValue(DateEntry)
End Function
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply