How do we determine the date time format in a SQL Server instance.
Specifically I would like to know, if the date time data type in SQL Server
is Language Specific or Language Neutral.
We are facing the following problem. I have a managed app, which is
localized. I need to update some data from the managed app to the
database(we are using MSDE). When I run the managed app in Italian locale,
with Italian build of MSDE, the database update fails.
The problem we figured out was, the date time cast in database fails. This
is because the time separator(for Italian locale) in .NET app is a period,
while in SQL MSDE(Italian build) it is a colon (
The following are my queries.
1. Is Date Time data type in SQL Language specific or Language Neutral? If
it is Language Neutral, I assume it will use the en-US culture, correct me
if I am wrong.
2. If date time is language specific, how is the collation set. Is it set by
default when MSDE is installed? Will the Operating System language version,
impact the collation, while installing MSDE.
3. When I run the query 'Select GetDate()' in Query Analyzer, the time
separator is displayed as a colon. Does the language version of SQL Server
tools(query analyzer/enterprise manager) have an impact on the date time
displayed?
Your inputs will help me a lot. Please reply to my ID (Ramjee_t@.infosys.com)
Thanks
RT
In message <ep1Idw#aFHA.580@.TK2MSFTNGP15.phx.gbl>, ramjee
<ramjee_t@.infosys.com> writes
>Hi
>How do we determine the date time format in a SQL Server instance.
>Specifically I would like to know, if the date time data type in SQL Server
>is Language Specific or Language Neutral.
>We are facing the following problem. I have a managed app, which is
>localized. I need to update some data from the managed app to the
>database(we are using MSDE). When I run the managed app in Italian locale,
>with Italian build of MSDE, the database update fails.
>The problem we figured out was, the date time cast in database fails. This
>is because the time separator(for Italian locale) in .NET app is a period,
>while in SQL MSDE(Italian build) it is a colon (
>The following are my queries.
>1. Is Date Time data type in SQL Language specific or Language Neutral? If
>it is Language Neutral, I assume it will use the en-US culture, correct me
>if I am wrong.
Not exactly. Physically in the database it is always stored the same way
however, the collation order does determine some of the supported
formats displaying and updating a DateTime field.
>2. If date time is language specific, how is the collation set. Is it set by
>default when MSDE is installed? Will the Operating System language version,
>impact the collation, while installing MSDE.
The default collation order is set when the instance of MSDE is
installed. However under MSDE 2000 / SQL Server 2000 the collation order
of each database can be different. Thats up to you when you CREATE the
DATABASE (ie: you determine the default collation order for each
database). In addtion, you can specify the collation order to use on
each Table and Field if really required. Check BOL for the CREATE
DATABASE and TABLE. You are therefore quite capable of using the same
collation order for every instance of MSDE you install regardless of
country.
>3. When I run the query 'Select GetDate()' in Query Analyzer, the time
>separator is displayed as a colon. Does the language version of SQL Server
>tools(query analyzer/enterprise manager) have an impact on the date time
>displayed?
Its all about handling dates in a consistent manor.
Its generally a good idea to always update a DataTime field using the
universal format "yyyy-mm-dd hh:nn:ss". By doing this, MSDE never gets
confused about which part is the month and day (ie: 2005-01-05 is always
5th Jan whereas 05-01-2005 could be 5th Jan or 1st May). Again, this
also solves international differences.
Its also therefore generally a good idea to always retrieve the DateTime
in a known format. Therefore using a command like "SELECT
Convert(datetime, MyDateField, 102) as MyDate FROM ..." would always
return the date in a UK format for example. That way your application
does not get confused and the localisation to the client is left to your
application.
>Your inputs will help me a lot. Please reply to my ID (Ramjee_t@.infosys.com)
No Problem.
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com
|||Hi Andrew,
You are mixing up collation, which is a property of character type columns
and variables in SQL Server, and the language that can be set for a
connection or user. The last one determines how dates as strings are
interpreted.
1) You are right that datetime and smalldatetime in SQL Server are stored in
a binary, language-neutral format. How the datetimes are displayed depends
on the client application however. For example Query Analyzer will by
default display dates in yyyy-mm-dd hh:mm:ss format. Enterprise Manager on
the other hand will use your Windows local settings to decide the display
format. How dates as strings are interpreted when inserting, updating or
deleting depends on the language setting for the connection, which are by
default derived from the language settings for the current user, although
they can be set explicitly with SET LANGUAGE.
2) As I said earlier, collation is irrelevant for datetime. The default
language settings for the user (login) are derived from the language in
which SQL Server is installed, but can be specified explicitly when creating
the login, or changed afterwards.
3) "yyyy-mm-dd hh:nn:ss" is not a safe format for datetime. Try the
following:
SET LANGUAGE us_english
SELECT CAST('2005-06-14 00:00:00' AS DATETIME)
GO
SET LANGUAGE british
SELECT CAST('2005-06-14 00:00:00' AS DATETIME)
There are 2 safe date formats in SQL Server:
yyyymmdd
and
yyyy-mm-ddThh:mm:ss
It is _not_ a good idea to always retrieve the datetime in a known string
format. Just retrieve the datetime as datetime, and let your application and
your user decide how to display is in a human-readable format. A properly
designed application will just use the Regional Settings from Windows to
decide how to display dates, and if you return datetime in a string format,
you just end up converting datetime values twice.
Jacco Schalkwijk
SQL Server MVP
"Andrew D. Newbould" <newsgroups@.NOzadSPANsoft.com> wrote in message
news:n0wpLfBXbrpCFwsj@.zadsoft.gotadsl.co.uk...
> In message <ep1Idw#aFHA.580@.TK2MSFTNGP15.phx.gbl>, ramjee
> <ramjee_t@.infosys.com> writes
> Not exactly. Physically in the database it is always stored the same way
> however, the collation order does determine some of the supported formats
> displaying and updating a DateTime field.
>
> The default collation order is set when the instance of MSDE is installed.
> However under MSDE 2000 / SQL Server 2000 the collation order of each
> database can be different. Thats up to you when you CREATE the DATABASE
> (ie: you determine the default collation order for each database). In
> addtion, you can specify the collation order to use on each Table and
> Field if really required. Check BOL for the CREATE DATABASE and TABLE. You
> are therefore quite capable of using the same collation order for every
> instance of MSDE you install regardless of country.
>
> Its all about handling dates in a consistent manor.
> Its generally a good idea to always update a DataTime field using the
> universal format "yyyy-mm-dd hh:nn:ss". By doing this, MSDE never gets
> confused about which part is the month and day (ie: 2005-01-05 is always
> 5th Jan whereas 05-01-2005 could be 5th Jan or 1st May). Again, this also
> solves international differences.
> Its also therefore generally a good idea to always retrieve the DateTime
> in a known format. Therefore using a command like "SELECT
> Convert(datetime, MyDateField, 102) as MyDate FROM ..." would always
> return the date in a UK format for example. That way your application does
> not get confused and the localisation to the client is left to your
> application.
>
> No Problem.
> --
> Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
> ZAD Software Systems Web : www.zadsoft.com
No comments:
Post a Comment