August 30, 2005 at 12:24 pm
Am passing a date value to a stored procedure via a report in access. values such as 1/1/03 work correctly, however when I try and pass 31/12/03 I recieve the error msg 'Run-time error '8114' Error converting data type nvarchar to datetime.' I have my PC regional settings set to 'English(Australia)' but I understand SQL by default functions under US date format. Is anyone aware at what point in the process I need to reformat the date or to change SQL servers date setttings?
August 30, 2005 at 12:27 pm
can you try to have your settings use 4 digits for hte year part?? That sould solve it.
August 30, 2005 at 12:37 pm
didn't do anything unfortunately, problem seems to be when access passes sp date in format dd/mm/yyyy when it is expecting it in the format mm/dd/yyyy, which is why it gets all flustered and thinks its a nvarchar not a datetime value. thus 12/31/1994 works but 31/12/1994 doesn't
August 30, 2005 at 12:48 pm
believe it should work if you set the style to 103..
CONVERT(varchar, '31/12/1994', 103)
**ASCII stupid question, get a stupid ANSI !!!**
August 30, 2005 at 12:52 pm
Also - in your odbc data source administrator in the control panel you can check the box that says "Use regional settings when outputting currency, numbers, dates and times"!!!
**ASCII stupid question, get a stupid ANSI !!!**
August 30, 2005 at 11:57 pm
Always use the ISO date format "yyyymmdd" and you will win, no matter the SQL Server's date format setting or the user's date format setting. (MDY, YMD, or DMY)
In Access you can use the Format(mydate,"yyyymmdd") function to convert a date to the ISO format.
Andy
August 31, 2005 at 12:31 am
Most definately use ISO format. All date formatting functions (in t-sql, vba, access, etc) understand this the easiest.
CONVERT(CHAR(8), datetime column, 112)
will display an existing datetime/smalldatetime value in ISO format.
Julian Kuiters
juliankuiters.id.au
August 31, 2005 at 4:54 am
you could use "SET DATEFORMAT dmy" at the beginning of the sproc...
Best regards
karl
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply