Translation of returned values by DATENAME

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Translation of returned values by DATENAME

Author

Topic

Johnyalm
Starting Member

49 Posts

Posted - 2005-12-13 : 09:13:44

I would like to get translated values (name fo months) when using datename!

I have a query like this

SELECT DISTINCT
DATENAME([month]
, a_tblTIMESERIES.[date]) AS monthname
, DATEPART(yy, a_tblTIMESERIES.[date]) AS [year]
, DATEPART(mm, a_tblTIMESERIES.[date]) AS monthnr
FROM
a_tblTIMESERIES
INNER JOIN
a_tblTIMESERIES_TYPE ON a_tblTIMESERIES.id_timeseries_type = a_tblTIMESERIES_TYPE.id_timeseries_type
WHERE
(a_tblTIMESERIES.id_actors = 8437)
AND (a_tblTIMESERIES_TYPE.type = 'steps')
ORDER BY monthnr

I the get for example

April 2005 4
May 2005 5
June 2005 6

But I need to get the monthnames in swedish likte this

April 2005 4
Maj 2005 5
Juni 2005 6

Is there any trick that can do this other than using a tem table and then Update the temp table with swedish spelled monthnames?

www.mirrorgate.com

Kristen
Test

22859 Posts

Posted - 2005-12-13 : 09:15:52

select datename([month], GetDate())

gives me "December" - not sure what you mean by "I would like to get translated values (name fo months) when using datename!"

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-13 : 09:21:03

You should give us sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

Johnyalm
Starting Member

49 Posts

Posted - 2005-12-13 : 09:30:20

Ok, I do not think data is needed the problem I has can be solved I suppose without data.

My question is "Can I set a directive to the server to delvier monthname in my native language instead or english?"

I will use this for generating a listbox for people to select from and I would like to automate the process from SQL server so that the result (Monthname) are presented by the server in sweidish instead of english!

quote:
Originally posted by madhivanan

You should give us sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

www.mirrorgate.com

Kristen
Test

22859 Posts

Posted - 2005-12-13 : 09:47:38

"Can I set a directive to the server to delvier monthname in my native language instead or english?"

It will be in the "locale" of the SQL Server box.

Better to do the type of processing you want client-side, OR have a language translation table in your application database) with the names of the months and JOIN to that.

Kristen

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-12-13 : 10:09:21

SQL Server has a table with the names on the Months and Weekdays in all languages its supports, but it is denormalized into a comma delimited list. You can look at it with this code.


select
	months,
	shortmonths,
	days
from
	master.dbo.syslanguages
where
	alias in ('English','Swedish')

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2005-12-13 : 10:45:21

Never knew that ... useful though! Ta!

Kristen

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-12-13 : 11:19:41

You can change the default language for SQL Server at the server level this way (From SQL Server Books Online):

"How to set the default language (Enterprise Manager)
To set the default language:
Expand a server group.
Right-click a server, and then click Properties.
Click the Server Settings tab.
In the Default language for user box, choose the language in which Microsoft® SQL Server™ should display system messages.
The default language is English."

You can change the default language for a login this way (From SQL Server Books Online):

"sp_defaultlanguage
Changes the default language of a login.

Syntax
sp_defaultlanguage @loginame = 'login' , @language = 'language'"

You can set the language at the session level this way:

SET LANGUAGE swedish
select datename(weekday,getdate())

Changed language setting to Svenska.

------------------------------
tisdag

(1 row(s) affected)

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-14 : 00:40:23

MVJ, you are really a DATETIME specialist

Madhivanan

Failing to plan is Planning to fail

Johnyalm
Starting Member

49 Posts

Posted - 2005-12-14 : 01:33:01

Thanks a lot! I knew there would be an easy way to do this - but I did not find in BOL myself!

cheers,

Johny Alm

quote:
Originally posted by Michael Valentine Jones

You can change the default language for SQL Server at the server level this way (From SQL Server Books Online):

"How to set the default language (Enterprise Manager)
To set the default language:
Expand a server group.
Right-click a server, and then click Properties.
Click the Server Settings tab.
In the Default language for user box, choose the language in which Microsoft® SQL Server™ should display system messages.
The default language is English."

You can change the default language for a login this way (From SQL Server Books Online):

"sp_defaultlanguage
Changes the default language of a login.

Syntax
sp_defaultlanguage @loginame = 'login' , @language = 'language'"

You can set the language at the session level this way:

SET LANGUAGE swedish
select datename(weekday,getdate())

Changed language setting to Svenska.

------------------------------
tisdag

(1 row(s) affected)

CODO ERGO SUM

www.mirrorgate.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources