December 16, 2010 at 10:03 am
All -
I am currently in the process of centralizing a Spanish application here in the US. The application was running on a Spanish Version of SQL Server. The one I have here is English. The vendor said that it didn't matter if we used English or Spanish during spec gathering. Now, with go-live close, we've discovered that the application isn't handling dates correctly. Is there anything I can do from a SQL Server side of things? The environment is shared, so I can't change the default language or any kind of regional settings. The database is already in a Spanish collation. The default language is not Spanish, but I'm not sure if that makes a difference in terms of date display when using a datetime data type (the SQL Server uses the ISO standard yyyy-mm-dd).
If I'm reading in a date 08-01-2010, it displays January as the month for my Spanish user and August for my English user.
If I'm reading in a date 2010-08-01, it displays August as the month for both the Spanish and English users.
Thanks for your help in advance!
December 16, 2010 at 10:38 am
Format inputs in ISO style, and you won't have any problem with it.
Are dates being fed to the database as strings, or as date-time data? How does the application categorize them?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 16, 2010 at 10:54 am
GSquared (12/16/2010)
Format inputs in ISO style, and you won't have any problem with it.Are dates being fed to the database as strings, or as date-time data? How does the application categorize them?
Yup, I already told them they should be formatted in ISO style. Most of the dates are being fed as date-time data, with the exception of a couple. The problem is encountered when the application reads back the data and displays it. I haven't seen the application since it is proprietary.
December 16, 2010 at 11:14 am
Interesting.
That means there's something going on in the application or its connection to the database.
SQL Server stores dates as decimal numbers. Left of the decimal is the number of days since "Day 0" (1/1/1900 in most installations), and right is number of 300ths of a second since midnight on that day. The collation data just affects the default output for select statements, it doesn't affect the actual storage. Most applications can read the data in a more native format, regardless of collation, and format it for local use. Even Excel can do that. (And stores dates and times the same way.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply