|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Saving to a DateTime field in Sql Server 2000 using ADO.Netthis from within a Winforms application. I have found the format of the datetime value sent to Sql server is different according to whether I send the value directly or use a SqlParameter. In the former case I get an error message if the day or month is higher than 12, as the 'm/d/y' date format appears to be used when Sql server receives the data, whereas it is expecting a 'd/m/y' format (although it is sent in the correct format if SqlParameter is used). I am in the UK and have my computer regional setting set to UK. I used the following code to demonstrate the problem (form with only a button1 and checkbox1): private void button1_Click(object sender, EventArgs e) { DateTime dt = dateTimePicker1.Value; SqlCommand com = new SqlCommand(); SqlConnection conn = new SqlConnection(connectionstring); com.Connection = conn; conn.Open(); if (checkBox1.Checked == true) { com.Parameters.Add(new SqlParameter("@dt" ,dt)); com.CommandText = @"INSERT INTO [dbo].[Test]([DueDate]) VALUES (@dt)"; //OK } else { com.CommandText = @"INSERT INTO [dbo].[Test]([DueDate]) VALUES ('" + dateTimePicker1.Value.ToString() + "')"; //error if day/month > 12 } com.ExecuteNonQuery(); conn.Close(); } Can anyone please help me understand why this is happening. I would expect UK formats to be used throughout by default. I am using VS 2005 beta 2. Thanks Tab,
This is often asked, the answer is simple. Try to avoid to use in any way in your application a string as data element. It is an element to show or to get from a user. (The dateTimePicker.value gives direct back a DateTime.) However you should add that than to the parameter) (Asuming that in your SQL database the date and time are as DateTime or ShortDateTime) This is a VBNet sample, http://www.vb-tips.com/default.aspx?ID=886bba68-8a2f-4b99-8f66-7139b8970071 Where in C# for that CDate if the value is already in datetime you can avoid that and if it is a string than you can use a datetime parse or a convert method. I hope this helps, Cor Cor,
Thanks for the reply. My understanding is that Sql server can't accept a DateTime object parameter (or any other object) - ultimately all requests to Sql Server are converted to strings. A Sql parameter must (therefore, I guess) convert its DateTime object to a string value, and it must do it in a different way than C#'s ToString. Is the above understanding correct? (if not, I will be very grateful if you could set me straight :) ) If it is correct, then in what sense should we avoid using a string as a data element? (I don't think you mean within C# we should only use types other than string as fields as strings are appropriate for names etc). I am aware dateTimePicker.Value returns a DateTime, but doesn't this have to be converted to a string to be sent to Sql Server (either directly or using a SqlParameter) Sorry if I have misunderstood your post. tab Show quote "Cor Ligthert [MVP]" wrote: > Tab, > > This is often asked, the answer is simple. > > Try to avoid to use in any way in your application a string as data element. > It is an element to show or to get from a user. (The dateTimePicker.value > gives direct back a DateTime.) > > However you should add that than to the parameter) > > (Asuming that in your SQL database the date and time are as DateTime or > ShortDateTime) > > This is a VBNet sample, > > http://www.vb-tips.com/default.aspx?ID=886bba68-8a2f-4b99-8f66-7139b8970071 > > Where in C# for that CDate if the value is already in datetime you can avoid > that and if it is a string than you can use a datetime parse or a convert > method. > > I hope this helps, > > Cor > > > Cor,
I did look at the sample, thanks; however, my main query was why the different result when using parameters over ToString - mainly an academic point as I have changed my code to using parameters and it works fine. Thanks for your help Show quote "Cor Ligthert [MVP]" wrote: > Tab, > > Did you try that simple sample? > > Cor > > > Here's what could be happening :
When SQL Server logins are created, the login ids get the default language of "us_english" and dateformat of "mdy". i.e. this seems independent of the regional setting of Windows. Running DBCC USEROPTIONS in the context of the login id you are using should confirm this. If you wanted to experiment further, try changing the default language of the login to "British English" (which will also have the effect of changing the dateformat to "dmy") as follows : == From SQL Server 2000 BOL : How to change the default language of a login (Enterprise Manager) To change the default language of a login Expand a server group, and then expand a server. Expand Security, and then click Logins. In the details pane, right-click the login to modify, and then click Properties. In the Language list, on the General tab, click the new default language in which messages are to be displayed to the user. == Note that "English" in Enterprise manager appears to be equivalent to "us_english" in DBCC USEROPTIONS. (I think you can even override the login's language in the SQL Server connection string.) HTH, Stephen Show quote "tab" <t**@discussions.microsoft.com> wrote in message news:826BF4C0-3D68-4F94-BB76-E92B9B371C7E@microsoft.com... > Cor, > > I did look at the sample, thanks; however, my main query was why the > different result when using parameters over ToString - mainly an academic > point as I have changed my code to using parameters and it works fine. > Thanks Stephen
Given the behaviour of SQL server (i.e apparantly ignoring regional settings) I imagine non US developers will always need to explicitly override the language setting Show quote "Stephen Ahn" wrote: > Here's what could be happening : > > When SQL Server logins are created, the login ids > get the default language of "us_english" and > dateformat of "mdy". i.e. this seems independent > of the regional setting of Windows. > > Running DBCC USEROPTIONS in the > context of the login id you are using > should confirm this. > > If you wanted to experiment further, > try changing the default language of > the login to "British English" > (which will also have the effect > of changing the dateformat to "dmy") > as follows : > > == From SQL Server 2000 BOL : > > How to change the default language of a login > (Enterprise Manager) > > To change the default language of a login > Expand a server group, and then expand a server. > Expand Security, and then click Logins. > > In the details pane, right-click the login to modify, > and then click Properties. > > In the Language list, on the General tab, click the > new default language in which messages are to be > displayed to the user. > == > > Note that "English" in Enterprise manager appears to > be equivalent to "us_english" in DBCC USEROPTIONS. > > (I think you can even override the login's language > in the SQL Server connection string.) > > > HTH, > Stephen > > > "tab" <t**@discussions.microsoft.com> wrote in message > news:826BF4C0-3D68-4F94-BB76-E92B9B371C7E@microsoft.com... > > Cor, > > > > I did look at the sample, thanks; however, my main query was why the > > different result when using parameters over ToString - mainly an academic > > point as I have changed my code to using parameters and it works fine. > > > > > Actually, best practice is to pass date/time as DateTime parameters where
possible. If a date/time must be passed as a string, then use one of the language/culture independent formats : http://www.karaszi.com/SQLServer/info_datetime.asp#DtFormatsInput I think there's no need to actually override the language setting. Stephen. Show quote "tab" <t**@discussions.microsoft.com> wrote in message news:D2B1B951-8B1B-4972-99AD-000A30D17638@microsoft.com... > Thanks Stephen > > Given the behaviour of SQL server (i.e apparantly ignoring regional > settings) I > imagine non US developers will always need to explicitly override the > language setting > > Thanks again
I actually didn't know you could use SqlParameter when not calling a stored procedure - now I do and I agree it's better. Would I be correct in thinking the SqlParameter does whatever is necessary behind the covers to ensure date formats are consistent with Sql Server? Show quote "Stephen Ahn" wrote: > > Actually, best practice is to pass date/time as DateTime parameters where > possible. If a date/time must be passed as a string, then use one of the > language/culture independent formats : > > http://www.karaszi.com/SQLServer/info_datetime.asp#DtFormatsInput > > I think there's no need to actually override the language setting. > > Stephen. > > "tab" <t**@discussions.microsoft.com> wrote in message > news:D2B1B951-8B1B-4972-99AD-000A30D17638@microsoft.com... > > Thanks Stephen > > > > Given the behaviour of SQL server (i.e apparantly ignoring regional > > settings) I > > imagine non US developers will always need to explicitly override the > > language setting > > > > > > > Yes, as long as the SqlParameter's type is of DateTime, ADO.NET will take
care of things for you. By the way, in your original post, the parameter type was implicitly worked out by ADO.NET to be a DateTime type : DateTime dt; ... com.Parameters.Add(new SqlParameter("@dt" ,dt)); Some people think that it's better to explicitly set it, when you know the type upfront. eg. DateTime dt; ... SqlParameter pp = new SqlParameter("@dt", System.Data.SqlDbType.DateTime); pp.Value = dt; com.Parameters.Add(pp); HTH, Stephen Show quote "tab" <t**@discussions.microsoft.com> wrote in message news:25EE165F-A7A2-47C8-B949-D34030770891@microsoft.com... > > Would I be correct in thinking the SqlParameter does whatever is necessary > behind the covers to ensure date formats are consistent with Sql Server? > Thanks again - I have really appreciated your assistance.
Show quote "Stephen Ahn" wrote: > Yes, as long as the SqlParameter's type is of DateTime, ADO.NET will take > care of things for you. > > By the way, in your original post, the parameter type was implicitly worked > out by ADO.NET to be a DateTime type : > > DateTime dt; > ... > com.Parameters.Add(new SqlParameter("@dt" ,dt)); > > Some people think that it's better to explicitly set it, when you know the > type upfront. eg. > > DateTime dt; > ... > SqlParameter pp = new SqlParameter("@dt", System.Data.SqlDbType.DateTime); > pp.Value = dt; > com.Parameters.Add(pp); > > HTH, > Stephen > > > "tab" <t**@discussions.microsoft.com> wrote in message > news:25EE165F-A7A2-47C8-B949-D34030770891@microsoft.com... > > > > Would I be correct in thinking the SqlParameter does whatever is necessary > > behind the covers to ensure date formats are consistent with Sql Server? > > > > > Tab,
All the modern Microsoft products uses ticks not really date In the OS it starts at 1 januari from the year one and are ticks in units from 100 nanoseconds In SQL server the datetime start at the day the Georgian Calendar was introduced in Brittain and his colonies (1753) In SQL server the shortDateTime start at the first day of our previous century. If you use the right DateTime settings and only DateTime values, you should normally have no problem. (Or somebody should have played with the settings or whatever) I hope that this gives an idea Cor Thanks again
Show quote "Cor Ligthert [MVP]" wrote: > Tab, > > All the modern Microsoft products uses ticks not really date > In the OS it starts at 1 januari from the year one and are ticks in units > from 100 nanoseconds > In SQL server the datetime start at the day the Georgian Calendar was > introduced in Brittain and his colonies (1753) > In SQL server the shortDateTime start at the first day of our previous > century. > > If you use the right DateTime settings and only DateTime values, you should > normally have no problem. (Or somebody should have played with the settings > or whatever) > > I hope that this gives an idea > > Cor > > > If you really must pass a DateTime as a string, then use one
of SQL server's culture netural formats. i.e. YYYYMMDD (for date with zero time) or YYYY-MM-DDThh:mm:ss[.nnn] (for date with non-zero time) eg. the following should work : com.CommandText = @"INSERT INTO [dbo].[Test]([DueDate]) VALUES ('" + dateTimePicker1.Value.ToString("yyyyMMdd") + "')"; As for why you got the error using ToString(), try running the following SQL from Query Analyzer : DBCC USEROPTIONS What do the "language" and "dateformat" entries show ? HTH, Stephen Show quote "tab" <t**@discussions.microsoft.com> wrote in message news:70F93177-8C27-422C-B52D-5E14027C6292@microsoft.com... >I have a Datetime field in a Sql Server 2000 database, and need to update > this from within a Winforms application. > > I have found the format of the datetime value sent to Sql server is > different according to whether I send the value directly or use a > SqlParameter. In the former case I get an error message if the day or > month > is higher than 12, as the 'm/d/y' date format appears to be used when Sql > server receives the data, whereas it is expecting a 'd/m/y' format > (although > it is sent in the correct format if SqlParameter is used). I am in the UK > and have my computer regional setting set to UK. > > I used the following code to demonstrate the problem (form with only a > button1 and checkbox1): > > private void button1_Click(object sender, EventArgs e) > { > DateTime dt = dateTimePicker1.Value; > > SqlCommand com = new SqlCommand(); > SqlConnection conn = new SqlConnection(connectionstring); > com.Connection = conn; > conn.Open(); > > if (checkBox1.Checked == true) > { > com.Parameters.Add(new SqlParameter("@dt" ,dt)); > > com.CommandText = @"INSERT INTO [dbo].[Test]([DueDate]) > VALUES > (@dt)"; //OK > } > else > { > com.CommandText = @"INSERT INTO [dbo].[Test]([DueDate]) > VALUES > ('" + dateTimePicker1.Value.ToString() + "')"; //error if > day/month > 12 > } > > com.ExecuteNonQuery(); > conn.Close(); > } > > Thanks, Stephen - I'll try that.
Show quote "Stephen Ahn" wrote: > If you really must pass a DateTime as a string, then use one > of SQL server's culture netural formats. i.e. > > YYYYMMDD (for date with zero time) or > YYYY-MM-DDThh:mm:ss[.nnn] (for date with non-zero time) > > eg. the following should work : > > com.CommandText = @"INSERT INTO [dbo].[Test]([DueDate]) VALUES > ('" + dateTimePicker1.Value.ToString("yyyyMMdd") + "')"; > > As for why you got the error using ToString(), > try running the following SQL from Query > Analyzer : > > DBCC USEROPTIONS > > What do the "language" and "dateformat" entries > show ? > > HTH, > Stephen > > > "tab" <t**@discussions.microsoft.com> wrote in message > news:70F93177-8C27-422C-B52D-5E14027C6292@microsoft.com... > >I have a Datetime field in a Sql Server 2000 database, and need to update > > this from within a Winforms application. > > > > I have found the format of the datetime value sent to Sql server is > > different according to whether I send the value directly or use a > > SqlParameter. In the former case I get an error message if the day or > > month > > is higher than 12, as the 'm/d/y' date format appears to be used when Sql > > server receives the data, whereas it is expecting a 'd/m/y' format > > (although > > it is sent in the correct format if SqlParameter is used). I am in the UK > > and have my computer regional setting set to UK. > > > > I used the following code to demonstrate the problem (form with only a > > button1 and checkbox1): > > > > private void button1_Click(object sender, EventArgs e) > > { > > DateTime dt = dateTimePicker1.Value; > > > > SqlCommand com = new SqlCommand(); > > SqlConnection conn = new SqlConnection(connectionstring); > > com.Connection = conn; > > conn.Open(); > > > > if (checkBox1.Checked == true) > > { > > com.Parameters.Add(new SqlParameter("@dt" ,dt)); > > > > com.CommandText = @"INSERT INTO [dbo].[Test]([DueDate]) > > VALUES > > (@dt)"; //OK > > } > > else > > { > > com.CommandText = @"INSERT INTO [dbo].[Test]([DueDate]) > > VALUES > > ('" + dateTimePicker1.Value.ToString() + "')"; //error if > > day/month > 12 > > } > > > > com.ExecuteNonQuery(); > > conn.Close(); > > } > > > > > > >  |
|||||||||||||||||||||||
Other interesting topics