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 |
|
delpiero
Yak Posting Veteran
98 Posts |
 Posted - 2005-05-11 : 04:13:31
|
| Hi all, I have a stored procedure which accepts a datetime input parameter. What I am concerning is where I should set the dateformat: inside the stored procedure code or at the application code when I call the stored procedure? My test experiences told me that the if I set the dateformat at both of the above (e.g. set dateformat mdy in the stored procedure code, and set dateformat ymd in the code which calls the stored procedure), only the latter one is effective, I mean the final outcome is that the dateformat ymd would be treated correctly. Is there a default dateformat which can be set? Because I don't think I am able to include a line to "set dateformat ymd" every time I call the stored procedure in my application code.Thanks a lot,delpiero |
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2005-05-11 : 08:31:06
|
| HiIf you pass the date into the procedure in an unambiguous format (i.e. yyyymmdd) SQL Server will correctly interpret it, even if the dateformat is explicitly set to contradict it within the procedure.Mark |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-05-11 : 09:10:16
|
| You don't do any date formatting at all. If you have a stored procedure with date parameters, in your application you set the parameters equal to a date VALUE not a string in some format. i.e., you declare a variable of type "date", assign it a value, and set the parameter equal to that date value.Your application needs to ensure it can produce a valid datetime datatype, and then your application just sets the parameter equal to the value. The server settings for date formats is irrelevant. That's part of the whole reason why you should use parameters instead of concatenating SQL strings together manually; it takes formatting out of the picture and you can focus on passing DATA and not a bunch of formatted strings.- Jeff |
|
|
|
delpiero
Yak Posting Veteran
98 Posts |
Posted - 2005-05-12 : 04:48:22
|
| For Mark's case, I found that if I pass in 'yyyymmdd', the result is correct only if I add the SET DATEFORMAT dmy before executing the stored procedure. This is because the SET DATEFORMAT for a new session automatically goes to "mdy" if we don't explicitly set it. In this case, date overflow will occur if I enter '20041228', for example. delpiero |
|
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2005-05-12 : 05:36:24
|
| Not on my servers! I notice you're in Italy, so I presume this is governed by a regional server setting. Having said that, I get the same results with a session set to an Italian language environment...Mark |
|
|
|
delpiero
Yak Posting Veteran
98 Posts |
Posted - 2005-05-13 : 06:38:20
|
| Mark, I think you are right as I made a mistake to convert the datetime variable into another format within my stored procedure causing the date to be incorrect. The format 'yyyymmdd' should be unambigous and independent of what we set on DATEFORMAT and LANGUAGE. Many thanks,delpiero |
|
|
|
|
|
|
|
|
SET DATEFORMAT