First, Profiler is your friend. Use it to find out that SQL is actually submitted by you app.Never
trust a tool/dev environment, which can do anything it like with a datetime value before presenting
it in a human readable format for you.

Here's a backgrounder on datetime that might help:
http://www.karaszi.com/SQLServer/info_datetime.asp

Also, I strongly encourage you to keep datetime values as date datatypes in your host language, and
use parameterized queries instead of constructing datetime literal. this way, it will always work.
And you also gets tons of other benefits from using parameterized queries.

Because there is no 13th month...

No.

It doesn't, unless you parameterise it.

Replace dbeg with dbeg.ToString("dd MMM yyyy") - you may have to cast dbeg
to a DateTime first...

You are passing these dates in as strings. You really should build
parameters for the statement or better yet use stored procedures. But in any
case what is the dateformat set to for that connection?  See SET DATEFORMAT
in BooksOnLine for more details. Since you are passing it as a string SQL
Server will try to convert it to a Datetime but it needs to know which is
the month, day, year etc.  The preferred way to deal with Dates as strings
is to use the ISO or ANSI formats so there is never a mistake in this
regard. For instance the date in this format will always work regardless of
language or date settings.    'yyyymmdd'   See here for more details as
well:

http://www.karaszi.com/SQLServer/info_datetime.asp
Guide to Datetimes
http://www.sqlservercentral.com/columnists/bsyverson/sqldatetime.asp
Datetimes
http://www.murach.com/books/sqls/article.htm
Datetime Searching

Hello Mark,

All your problems will go away if you start using parameters instead of inlining
the date as a string.

DateTime sbeg = new DateTime(2007,2,8);

comd.CommandText = "insert into mytable (datbegin,datend) values(@dstart,
@dend)"
comd.Parameters.Add(new SqlParameter("@dstart", dbeg));
comd.Parameters.Add(new SqlParameter("@dend", dend));

This will mak

Hello Mark,

There are 3 solutions, 2 at the .NET side, 1 at the SQL side

1) All your problems will go away if you start using parameters instead of
inlining the date as a string. This is the best solution you could choose.

   DateTime dbeg = new DateTime(2007,2,8);
   DateTime dend = new DateTime(2007,2,13);
   comd.CommandText = "insert into mytable (datbegin,datend) values(@dstart,
@dend)";
   comd.Parameters.AddWithValue("@dstart", dbeg);
   comd.Parameters.AddWithValue("@dend", dend);

This will make sure the data is passed as a DateTime.

2) Another option is to format the dates you pass to the query in the following
format: yyyy.mm.dd. You can use string.format to do that easily:

   comd.CommandText = string.Format("insert into mytable (datbegin,datend)
values({0:yyyy.MM.dd}, {1:yyyy.MM.dd})", dbeg, dend);

3) You could add a function around your inserted strings to parse the date
in de SQL statement.

   comd.CommandText = string.Format("insert into mytable (datbegin,datend)
values(convert(datetime, {0}, 105), convert(datetime, {1}, 105))", dbeg,
dend);

More info on the style id's (105 in this case) can be found here: http://sqljunkies.com/HowTo/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk

Whichever route you choose consider this:
- have a look at the string.Format function. It's your bets friend when concatenating
lots of strings together in a readable fashion. A
- have a look at parameters for SQL queries. They're faster, easier to read
and maintain and more secure to boot. They also have less trouble with conversions
as you'll find out.

Jesse