DateTime Automatic Formatting

[bryars]

I want to write some SQL which results in an automatic conversion of a
datetime to a string in a format suitable for the Language of the
connection (either by explicitly setting the Language in the
connection string, or by setting the default language in for the user
used for the connection.)

The casting from string to datetime uses the language setting:

Data Source=localhost\sqlexpress;Initial Catalog=master;Persist
Security Info=True; Language =BRITISH ENGLISH; Trusted_Connection=yes;
Application Name = Test Application;

select cast('13/01/2008' as datetime) --WORKS as expected
select cast('01/13/2008' as datetime) --FAILS as expected

and

Data Source=localhost\sqlexpress;Initial Catalog=master;Persist
Security Info=True; Language =ENGLISH; Trusted_Connection=yes;
Application Name = Test Application;

select cast('13/01/2008' as datetime) --FAILS as expected
select cast('01/13/2008' as datetime) --WORKS as expected

but implicit casting the other way ignores the "Language setting" (ie
the format is the same for both):

BRITISH ENGLISH
select cast(cast('12/01/2008' as datetime) as nvarchar(max)) --Jan 12
2008 12:00AM
select convert(nvarchar(max),cast('12/01/2008' as datetime)) --Jan 12
2008 12:00AM

ENGLISH
select cast(cast('12/01/2008' as datetime) as nvarchar(max)) --Dec 1
2008 12:00AM
select convert(nvarchar(max),cast('12/01/2008' as datetime)) -- Dec 1
2008 12:00AM

Is it possible to tell SQL Server "For language w convert datetimes to
strings using format x, but for language y use format z?"

Regards,

Dan

[Plamen Ratchev]

Seems to work just fine to me when the correct language is set:

SET LANGUAGE british
SELECT CAST(CAST('12/01/2008' AS DATETIME) AS NVARCHAR(30))
Result:
Jan 12 2008 12:00AM

SELECT CONVERT(NVARCHAR(30), CAST('12/01/2008' AS DATETIME))
Result:
Jan 12 2008 12:00AM
SET LANGUAGE us_english
SELECT CAST(CAST('12/01/2008' AS DATETIME) AS NVARCHAR(30))
Result:
Dec 1 2008 12:00AM

SELECT CONVERT(NVARCHAR(30), CAST('12/01/2008' AS DATETIME))
Result:
Dec 1 2008 12:00AM
I am not sure if there is a difference when setting the language via the
connection string.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[Erland Sommarskog]

(br****@hotmail.com) writes:

I want to write some SQL which results in an automatic conversion of a
datetime to a string in a format suitable for the Language of the
connection (either by explicitly setting the Language in the
connection string, or by setting the default language in for the user
used for the connection.)

I'm sorry, but why on Earth would you do that? Return datetime data as
binary values to the client and do the formatting there.

The datetime and language settings in SQL Server applies only for how
strings are interpreted. They do not affect output.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx