CODEWITHSUNDEEP
sql-server-2005t-sqllocalization
Elynn Chia
Elynn Chia

Reputation: 7

SQL language dependent conversion (number to month name)

I would like to convert number to month name, language dependent. For example:

How to do that? My purpose is to convert to my country language which is BAHASA MALAYSIA (JANUARI , FEBRUARI , MAC ... disember).

Upvotes: 1

Views: 2927

Answers (3)

Gargo
Gargo

Reputation: 704

I don't know how to write it correctly for your kind of database. The main idea is:

result = to_char(date, MM)
result = REPLACE('1', "JANUARI", result)
result = REPLACE('2', "FEBRUARI", result)
....

And at least in Oracle it can be written without stored procedures

Upvotes: 0

gbn
gbn

Reputation: 432210

You can't localise in SQL Server in this fashion: you can use one language per query
This language setting is what DATENAME uses

So unless you want a huge number of lookup tables (one per language), then do this on the client.

DATENAME is also unreliable for some languages. See DATENAME(MONTH,GETADATE()) is returning numeric value of the month as '09'

SQL Server 2012 adds some culture setting via FORMAT but you'd have to pass this into SQL Server. Like I said, do this in the client.

Edit:

Indonesian isn't supported in SQL Server 2005 via SET LANGUAGE.

So, for this one language you'd need a CASE

CASE DATEPART(month, GETDATE())
  WHEN 1 THEN 'januari '
  WHEN 2 THEN '...'
  ...
END

Otherwise, SET LANGUAGE or do it in the client

Upvotes: 3

Gene
Gene

Reputation: 4232

In my opinion, localization shouldn't be done on server/business logic level if you can avoid it. Therefore, I would return the date/month in the stored procedure as a DATETIME or INT and localize this information in the client which handles the data.

Upvotes: 1

Related Questions