Post reply

changing regional settings

  • January 7, 2008 at 1:55 am

    #73947

    SQL 2000 std

    windows 2003 server

    DB: Navision

    Here in Britian, someone has setup a server and SQL database as English (US).

    now all the date fields are shown in US month/day/year. I'm not sure if this is done by design or by fault.

    my question is.. if I change it back to UK, would this have any effect on the data?

    I was under the impression that the data is always stored in US and presented back to British?

    can SQL server be changed back to UK after been setup as US?

    Thanks in advance

    Dave

  • January 7, 2008 at 8:42 am

    #765375

    The setting is saved in the database, master. If you do not specify the setting in creating a new datbase, your newly created database will have the same setting as defined in the master. You cannot change the setting in the master unless you rebuild it.

    You can change the setting in creating a new database by providing your desired setting, or alter the setting in an existing database, even in a table level.

    To get more information, browse the information on collation.

  • January 7, 2008 at 8:49 am

    #765384

    The data is stored the same no matter what your regional setting is. What changes is the way dates are presented, and the way SQL Server interprets dates that are passed to it.

    John

  • January 7, 2008 at 8:52 am

    #765386

    ok

    Many thanks for the reply.

    Dave

  • January 7, 2008 at 8:58 am

    #765393

    yeah, I thought that was the case John.

    I've requested that dates are handled in the following manner when inserting and retrieving. 07\Jan\2008. this way, the SQL / Client will always know that we're dealing with dates in a certain manner..

    All this cropped up, because someone was inserting dates and they would appear in the wrong format. e.g. 04/01/2007 was coming out as 1st/April/2007 instead of being 4th/jan/2007... The latter half of the month was fine ( after the 12th of each month, as SQL knew that the day part was more then 12) if that makes sense..

    all sorted now anyway..

    thanks for the replies

    Dave

  • January 7, 2008 at 9:09 am

    #765397

    When you want to pass character date strings to SQL Server, the best format to use is YYYYMMDD, example: 20080131.

    This format is always interpreted the same by SQL Server, not matter what the setting of dateformat or language is.

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

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