datetime in MSSQL 2008

  • hi everyone (my first post)

    I have a strange situation. Because of regional settings (slovenian) in MSSQL 2005 the datetime field was formed as 3.4.2010 00:... (meaning third of April..).

    When I copied the database in 2008 R2 and MSSQL 2008, the datetime changed (with data) in 2010-04-03 00:....

    Even if I create new database with single table and datetime type in a single column and enter "3.4.2010" SQL converts the date into 2010-04-03....

    The problem is serious because the applications (old applications, I don't have source code of them) can't read the date because it's not in right format... Of course I can't even change the code of application.

    So.. my question is next:

    Is this normal? Is this like some kind of standard in MSSQL 2008? Should I double, triple check the regional settings if they are set to Slovene in Windows 2008?

    thanks in a advance.

  • Yeah sql datetime is in the format u said.. if ur application cannot be changed then I guess create a varchar field and convert the date into format that is accepted by application and use that field.

  • You Can Achive this by adding Computed Column also

    CREATE TABLE [dbo].[tbl2](

    [field1] [datetime] NULL,

    [field2] [varchar](50) NULL,

    [field3] AS ((((CONVERT([varchar],datepart(day,[field1]),0)+'.')+CONVERT([varchar],datepart(month,[field1]),0))+'.')+CONVERT([varchar],datepart(year,[field1]),0))

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    Insert into [dbo].[tbl2] values ('2011-06-01 13:19:25.943',NULL)

    GO

    select * from [tbl2]

  • I'd check the server collation setting. I'm pretty sure they are not the same. Or the collation of the database has been changed in the move.

    Keep in mind that tempdb has its own collation and can be different from the one in your other databases.

  • @ninja's_RGR'us,

    hello, thanks for answering.

    I put a link to picture, where old sql2005 (left side) and new sql2008 (right side) are shown.

    http://www.nuk.uni-lj.si/dokumenti/sql.jpg

    The collation is the same..

  • What about the server collation, tempdb and connection settings?

  • Firstly, SQL Server is not converting your dates to a different format. A datetime is a datetime regardless of your regional settings. What's different is how SSMS and other client applications are turning this format into a string representation, it has nothing to do with how the data is stored.

    The only thing I can assume would break in a client application is if it's not using date types internally and is relying on implicit conversion from strings to create/update date values on the database.

    You need to look at the windows regional settings at both the server level and the client level, bearing in mind that these may be user specific, so you need to check them as the sql server service account and the account being used client-side

  • this sounds like a language/region issue not a collation issue.

    check to see if these settings are the same on your new server as the old one

  • I agree with Steve... language and region are probably the reason and should be compared old to new.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hi,

    still same problems.

    I triple checked regional settings.

    Regional / Format: Slovenian,

    Location - Slovenia

    Keyboard - slovenian

    so (input language, format, location) all set to slovenia.

    I'm logged as administrator and I have restarted the server also.

    If I create new database in Windows servwr 2003 with MSSQL 2005 with table date and field date

    and use getdate function, I get 1.6.2011 ....

    collation is the same, connection also.

    If I try the same in Windws server 2008, MSSQL 2008- yes, I get 2011-06-01...

    I can use local characters in cmd prompt, and other applications without problems.

    Since there are different aproaches in a thread of solving the situation I wonder

    Is it possible in MSSQL 2008 to store datetime in different format than 2011-06-06... (like it was in MSSQL 2005) without converting (so with regional settings definition) or isn't. I found several web pages where there is talk just about converting into appropriate format (select convert(varchar, getdate(), 4) )

    thanks for answers and help - again.

  • Feanor (6/1/2011)


    Hi,

    still same problems.

    I triple checked regional settings.

    Regional / Format: Slovenian,

    Location - Slovenia

    Keyboard - slovenian

    so (input language, format, location) all set to slovenia.

    I'm logged as administrator and I have restarted the server also.

    If I create new database in Windows servwr 2003 with MSSQL 2005 with table date and field date

    and use getdate function, I get 1.6.2011 ....

    collation is the same, connection also.

    If I try the same in Windws server 2008, MSSQL 2008- yes, I get 2011-06-01...

    I can use local characters in cmd prompt, and other applications without problems.

    Since there are different aproaches in a thread of solving the situation I wonder

    Is it possible in MSSQL 2008 to store datetime in different format than 2011-06-06... (like it was in MSSQL 2005) without converting (so with regional settings definition) or isn't. I found several web pages where there is talk just about converting into appropriate format (select convert(varchar, getdate(), 4) )

    thanks for answers and help - again.

    You didn't tell us about the connection settings between the old and new servers. Dateformat could have changed between the 2 or maybe something else.

  • hello, ninja (Anakin)

    if you mean connection settings i put them on this link

    (left side - old 2005mssql, right 2008mssql)

    http://www.nuk.uni-lj.si/dokumenti/sql-connect.jpg

    it seem pretty same to me...

  • That's SSMS you need to see what the app settings are once connected to the server.

    Start a default trace and paste the value of the event existing connection.

    It should look something like this :

    -- network protocol: TCP/IP

    set quoted_identifier on

    set arithabort off

    set numeric_roundabort off

    set ansi_warnings on

    set ansi_padding on

    set ansi_nulls on

    set concat_null_yields_null on

    set cursor_close_on_commit off

    set implicit_transactions on

    set language Français

    set dateformat dmy

    set datefirst 1

    set transaction isolation level read uncommitted

    As a side note I once did an upgrade from windows 2000 server to 2003 and it completely wipped out our linked servers. Turns out it was a simple security setting change between the windows versions. It's not likely but it's a possible suspect.

  • ok, I made connection trace, but... it's he same:

    Win 2003 / MS sql 2005:

    -- network protocol: TCP/IP

    set quoted_identifier on

    set arithabort off

    set numeric_roundabort off

    set ansi_warnings on

    set ansi_padding on

    set ansi_nulls on

    set concat_null_yields_null on

    set cursor_close_on_commit off

    set implicit_transactions off

    set language us_english

    set dateformat mdy

    set datefirst 7

    set transaction isolation level read committed

    Win 2008 R2 / MS sql 2008

    -- network protocol: TCP/IP

    set quoted_identifier on

    set arithabort off

    set numeric_roundabort off

    set ansi_warnings on

    set ansi_padding on

    set ansi_nulls on

    set concat_null_yields_null on

    set cursor_close_on_commit off

    set implicit_transactions off

    set language us_english

    set dateformat mdy

    set datefirst 7

    set transaction isolation level read committed

    should I trace for something else, also?

  • Well I'm out of ideas.

Viewing 15 posts - 1 through 15 (of 26 total)

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