Saving to a DateTime field in Sql Server 2000 using ADO.Net

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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();
}


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
 
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
 
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
 
Thanks, Stephen - I'll try that.

Stephen Ahn said:
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 said:
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();
}
Click to expand...
Click to expand...
 
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
 
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
 
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

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?
 
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
 
Back
Top