Hello Mark,
Quote:
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
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...C7FD826E5.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
