Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dr S.A.f.
Starting Member
13 Posts |
 Posted - 2008-07-02 : 12:03:57
|
| Hi,I have the following issue. We have a webapp based on the csla framework that passes data to a stored procedure. One of the params is a parameter of the format datetime, and the format of the passed datetime var is like this: YYYY-MM-DD TT:TT:TT:TTTOn our development server this runs perfect, however on our production server, The stored procedure returns the error incorrect syntax near '2008this is how the sp is called viewed from sql server profiler.exec usp_AddFile @FILE_CUST_ID=80686,@FILE_NAME=N'',@FILE_CREATION_DATE=''2008-07-02 17:16:24:000'', etc...I assume this has something to do with an incorrect culture setting. Although both servers should be identical.I allready check the regional settings on both machines and they appear to be correct, I also checked the language of the sqluser used by the app, and it is also english.I'm sure that my sp is correct because I can execute the code that i fetch from profiler on the development server which has the same db running, I also know that I could get a workaround by sending the date as a varchar and convert it to the appropriate datetime format in de stored proc itself, but I still would like to know what it is that could cause this kind of behavior.thank you in advance(btw the server is a sql 2k5 server running on a 2k3 server) |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-07-02 : 12:12:52
|
| The easiest fix is to pass the data in this format:YYYYMMDD hh:mm:ss.milExample for today:20080702 13:44:55.997This is SQL Servers universal time format that will be work with any language or date format settings.CODO ERGO SUM |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-02 : 13:26:24
|
| or try explicitly changing the dateformat setting interpretation of server using SET DATEFORMAT |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-07-02 : 13:29:58
|
quote:
Originally posted by visakh16
or try explicitly changing the dateformat setting interpretation of server using SET DATEFORMAT
As long as you have control over the application, I would prefer doing it thereMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Stored proc doesn't accept datetime param