problem with date

Discussion in 'ASP .Net' started by Mark, Aug 2, 2007.

  1. Mark

    Mark Guest

    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
    Mark, Aug 2, 2007
    #1
    1. Advertising

  2. 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.
    --
    Tibor Karaszi, SQL Server MVP
    http://www.karaszi.com/sqlserver/default.asp
    http://sqlblog.com/blogs/tibor_karaszi


    "Mark" <> wrote in message news:...
    > 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
    >
    Tibor Karaszi, Aug 2, 2007
    #2
    1. Advertising

  3. "Mark" <> wrote in message
    news:...

    > Why doesn't sql server accept those values?


    Because there is no 13th month...

    > They are conform the settings, no?


    No.

    > Or maybe the Insert command transforms the format of tye date?


    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...


    --
    Mark Rae
    ASP.NET MVP
    http://www.markrae.net
    Mark Rae [MVP], Aug 2, 2007
    #3
  4. 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

    --
    Andrew J. Kelly SQL MVP

    "Mark" <> wrote in message
    news:...
    > 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
    >
    Andrew J. Kelly, Aug 2, 2007
    #4
  5. 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



    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
    Jesse Houwing, Aug 2, 2007
    #5
  6. 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



    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
    Jesse Houwing, Aug 2, 2007
    #6
  7. Mark

    Mark Guest

    Thanks for replying


    "Jesse Houwing" <> schreef in bericht
    news:...
    > 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

    >
    >
    > 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
    >
    >
    Mark, Aug 2, 2007
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Matt
    Replies:
    1
    Views:
    595
    Matthew Speed
    Nov 8, 2003
  2. Peter Grison

    Date, date date date....

    Peter Grison, May 28, 2004, in forum: Java
    Replies:
    10
    Views:
    3,244
    Michael Borgwardt
    May 30, 2004
  3. Matt
    Replies:
    2
    Views:
    513
    Pete Becker
    Nov 8, 2003
  4. Matt
    Replies:
    3
    Views:
    718
    Richard Heathfield
    Nov 8, 2003
  5. Matt
    Replies:
    11
    Views:
    404
    Aaron Bertrand [MVP]
    Nov 8, 2003
Loading...

Share This Page