Post reply

Insert date values in SQL 2000 --> Error

  • September 4, 2010 at 3:37 am

    #239937

    Hi,

    I am using SQL 2000

    I am inserting date values into the database table using the format

    insert into tableA( dateColumn) values ('2010-05-19 00:00:00')

    --the dateColumn is of datatype datetime.

    I Now the above query works fine

    I opened 2 instances of SQL Query Analyser. One both the instances, I am connected to the SQL Server and exactly the same database . When I execute the above insert statement, it works fine on one Query Analyser instance and gives the below error

    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.

    I observed that when I type letters from the keyboard, it showed different letters (especially the special characters like ()-+ ). This probably meant to say that the both the Sql query analysers are taking different regional settings.. Now my question is...

    I want the insert query to work irrespective of the Regional Settings selected for that query analyser . What is the technique to do this ? I know , I can go to control panel and change the regional language settings, but is there a way to change regional settings via some sql command ?

    Thanks

  • September 4, 2010 at 7:47 am

    #1216527

    If you want to insert a date without a time portion you can use the format YYYYMMDD.

    This will work independent of any language setting.

    As a side note: it's not the setting of LANGUAGE that will change the behavior. It's the setting of DATEFORMAT, that will cause the effect (DATEFORMAT usualy will be changed if the value for LANGUAGE is changed, but not always...)

    insert into tableA( dateColumn) values ('20100519')


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • September 4, 2010 at 9:33 am

    #1216533

    Before inserting,

    I SET DATEFORMAT ymd

    and now it works fine. I am no more getting this error. May be this is the solution as you suggested earlier

  • September 4, 2010 at 10:45 am

    #1216542

    msforumpost (9/4/2010)

    ...May be this is the solution as you suggested earlier

    No. The solution I was talking about will work regardless of any DATEFORMAT setting.

    If you use such a concept in your code you need to make sure to reset the value to it's original after you're done with your insert since it will affect the rest of the session. Therefore, I'd vote against it. Be carefule with changing settings just to make a code work. change the code to work without any specific setting instead. (e.g. by using the example I posted previously).


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • September 7, 2010 at 7:22 am

    #1217321

    Make sure to read this post to understand how datetimes work in SQL Server

    http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-ii.aspx

    Madhivanan

    Failing to plan is Planning to fail

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply