+ Reply to Thread
Results 1 to 5 of 5

datetime, sql server 2005, c#

  1. datetime, sql server 2005, c#


    An application inserts some data using something like:

    insert into mytable ( dateCol ) values ( '1/20/2007 3:02:14 PM' )

    the datetime string was created using DateTime.Now.ToString()

    Next, the user of the machine goes to the control panel/region settings and
    changes the short date format from M/d/yyyy to d/M/yyyy so from US dates to
    British dates. The new sql statement generated has the new formatting:

    insert into mytable ( dateCol ) values ( '20/1/2007 3:02:14 PM' )

    and this of course is out of range:

    Msg 242, Level 16, State 3, Line 1
    The conversion of a char data type to a datetime data type resulted in an
    out-of-range datetime value.
    The statement has been terminated.

    So, I guess sql server ignores the date change in this case. And in general
    the c# app could be talking to a sql server machine w/ any number of
    settings. So how I send such an insert string to sql server? Is there a
    general date formatter based on a db connection? I would prefer not to have
    to change the datetime column to something like a bigint and just use seconds
    since, for example.

    thanks,
    Paul.







  2. Re: datetime, sql server 2005, c#

    Avoid ambiguous date formats and you have no problem. The ISO format
    (YYYYMMDD HH:MM:SS.mmm) works, no matter what the locale stetting is. But
    why are you passing strings around? Why not just pass the DateTime instance
    itself and let SQL Server do the work?


    --

    Adam Machanic
    SQL Server MVP

    Author, "Expert SQL Server 2005 Development"
    http://www.apress.com/book/bookDisplay.html?bID=10220



    "paul" wrote in message
    news:AD6C2599-1480-4B17-A938-1E508F05A870@microsoft.com...
    >
    > An application inserts some data using something like:
    >
    > insert into mytable ( dateCol ) values ( '1/20/2007 3:02:14 PM' )
    >
    > the datetime string was created using DateTime.Now.ToString()
    >
    > Next, the user of the machine goes to the control panel/region settings
    > and
    > changes the short date format from M/d/yyyy to d/M/yyyy so from US dates
    > to
    > British dates. The new sql statement generated has the new formatting:
    >
    > insert into mytable ( dateCol ) values ( '20/1/2007 3:02:14 PM' )
    >
    > and this of course is out of range:
    >
    > Msg 242, Level 16, State 3, Line 1
    > The conversion of a char data type to a datetime data type resulted in an
    > out-of-range datetime value.
    > The statement has been terminated.
    >
    > So, I guess sql server ignores the date change in this case. And in
    > general
    > the c# app could be talking to a sql server machine w/ any number of
    > settings. So how I send such an insert string to sql server? Is there a
    > general date formatter based on a db connection? I would prefer not to
    > have
    > to change the datetime column to something like a bigint and just use
    > seconds
    > since, for example.
    >
    > thanks,
    > Paul.
    >
    >
    >
    >
    >
    >



  3. Re: datetime, sql server 2005, c#

    paul (paul@discussions.microsoft.com) writes:
    > An application inserts some data using something like:
    >
    > insert into mytable ( dateCol ) values ( '1/20/2007 3:02:14 PM' )
    >
    > the datetime string was created using DateTime.Now.ToString()
    >
    > Next, the user of the machine goes to the control panel/region settings
    > and changes the short date format from M/d/yyyy to d/M/yyyy so from US
    > dates to British dates. The new sql statement generated has the new
    > formatting:
    >
    > insert into mytable ( dateCol ) values ( '20/1/2007 3:02:14 PM' )
    >
    > and this of course is out of range:
    >
    > Msg 242, Level 16, State 3, Line 1
    > The conversion of a char data type to a datetime data type resulted in an
    > out-of-range datetime value.
    > The statement has been terminated.
    >
    > So, I guess sql server ignores the date change in this case. And in
    > general the c# app could be talking to a sql server machine w/ any
    > number of settings. So how I send such an insert string to sql server?
    > Is there a general date formatter based on a db connection? I would
    > prefer not to have to change the datetime column to something like a
    > bigint and just use seconds since, for example.


    You use parameterised statments:

    INSERT INTO mytable(DateCol) VALUES (@date)

    And then you define the parameter you specify the datatype, and the
    string will be converted to a binary format and interpreted by the
    regional settings. For an example of parameterised statements in .Net,
    see http://www.sommarskog.se/dynamic_sql.html#queryplans and scroll
    down a bit. (That text will also tell you other reasons why should
    send down unparameterised query strings.)


    --
    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Books Online for SQL Server 2005 at
    http://www.microsoft.com/technet/pro...ads/books.mspx
    Books Online for SQL Server 2000 at
    http://www.microsoft.com/sql/prodinf...ons/books.mspx

  4. Re: datetime, sql server 2005, c#


    Thanks Erland and Adam for the information!

    Your answers made me think of one other issue - bulk insert. How do you add
    a date to the bulk insert text file in a way that is region independent?
    Would the iso date format be the recommended approach? Luckily we don't bulk
    insert dates at the moment so I don't think this is a issue at the moment.

    thanks,
    Paul.


  5. Re: datetime, sql server 2005, c#

    paul (paul@discussions.microsoft.com) writes:
    >
    > Thanks Erland and Adam for the information!
    >
    > Your answers made me think of one other issue - bulk insert. How do you
    > add a date to the bulk insert text file in a way that is region
    > independent? Would the iso date format be the recommended approach?
    > Luckily we don't bulk insert dates at the moment so I don't think this
    > is a issue at the moment.


    YYYYMMDD is safe. YYYY-MM-DD is not. But YYYY-MM-DDThh:mm:ss is as is
    YYYY-MM-DDZ. (T and Z here stand for themselves.)

    But if you need to export to some other source than SQL Server, it may
    be that YYYY-MM-DD works better. Yes, it's a bit messy.


    --
    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Books Online for SQL Server 2005 at
    http://www.microsoft.com/technet/pro...ads/books.mspx
    Books Online for SQL Server 2000 at
    http://www.microsoft.com/sql/prodinf...ons/books.mspx

+ Reply to Thread