problem with date

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

Hi,

i have an application which works with date.
The regional settings of the computer (XP prof. dutch version) are set to
French (Belgium).
Asp.net and Sql server take the short date format of the regional settings
(e.g. 2/08/2007 or 13/08/2007).
I checked both: that's ok.

When i try to insert a date in a datetime field in sql server which is e.g.
13/08/2007, i get the error:
"The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value."

This is the code:
comd.CommandText = "insert into mytable (datbegin,datend) values('" & dbeg &
"','" & dend & "')"

I checked just before inserting the values (with response.write) and they
are: 2/08/2007 and 13/08/2007.

Why doesn't sql server accept those values? They are conform the settings,
no?
Or maybe the Insert command transforms the format of tye date?

Thanks for help
Mark
 
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.
 
Why doesn't sql server accept those values?
Click to expand...

Because there is no 13th month...
They are conform the settings, no?
No.

Or maybe the Insert command transforms the format of tye date?
Click to expand...

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,
Hi,

i have an application which works with date.
The regional settings of the computer (XP prof. dutch version) are set
to
French (Belgium).
Asp.net and Sql server take the short date format of the regional
settings
(e.g. 2/08/2007 or 13/08/2007).
I checked both: that's ok.
When i try to insert a date in a datetime field in sql server which is
e.g.
13/08/2007, i get the error:
"The conversion of a char data type to a datetime data type resulted
in an
out-of-range datetime value."
This is the code:
comd.CommandText = "insert into mytable (datbegin,datend) values('" &
dbeg &
"','" & dend & "')"
I checked just before inserting the values (with response.write) and
they are: 2/08/2007 and 13/08/2007.

Why doesn't sql server accept those values? They are conform the
settings,
no?
Or maybe the Insert command transforms the format of tye date?
Thanks for help
Mark
Click to expand...


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,
Hi,

i have an application which works with date.
The regional settings of the computer (XP prof. dutch version) are set
to
French (Belgium).
Asp.net and Sql server take the short date format of the regional
settings
(e.g. 2/08/2007 or 13/08/2007).
I checked both: that's ok.
When i try to insert a date in a datetime field in sql server which is
e.g.
13/08/2007, i get the error:
"The conversion of a char data type to a datetime data type resulted
in an
out-of-range datetime value."
This is the code:
comd.CommandText = "insert into mytable (datbegin,datend) values('" &
dbeg &
"','" & dend & "')"
I checked just before inserting the values (with response.write) and
they are: 2/08/2007 and 13/08/2007.

Why doesn't sql server accept those values? They are conform the
settings,
no?
Or maybe the Insert command transforms the format of tye date?
Thanks for help
Mark
Click to expand...


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
 
Thanks for replying


Jesse Houwing said:
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
Click to expand...
 
Back
Top