June 1, 2011 at 12:30 am
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.
June 1, 2011 at 12:43 am
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.
June 1, 2011 at 1:48 am
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]
June 1, 2011 at 5:58 am
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.
June 1, 2011 at 6:05 am
@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..
June 1, 2011 at 6:07 am
What about the server collation, tempdb and connection settings?
June 1, 2011 at 6:16 am
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
June 1, 2011 at 6:18 am
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
June 1, 2011 at 6:30 am
I agree with Steve... language and region are probably the reason and should be compared old to new.
--Jeff Moden
June 1, 2011 at 1:23 pm
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.
June 1, 2011 at 1:57 pm
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.
June 1, 2011 at 11:29 pm
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...
June 2, 2011 at 5:42 am
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.
June 2, 2011 at 7:13 am
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?
June 2, 2011 at 7:18 am
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
