SQL Server multiple date languages in same query

Question

On a datawarehouse project with SSIS/SSAS, I have to generate my own time dimension because I've personal data to integrate with.

My problem is with SSAS because I also need to integrate translation. After reading the documentation, I've found a command to set language for the current session by using SET LANGUAGE ENGLISH but I'm not able to change language for different field of the query.

Is there a way to generate MONTH_NAME in French and also get MONTH_NAME_DE in German ?

Here is the script that I've found on Internet

WITH Mangal as 
( 
    SELECT Cast ('1870-01-01' as DateTime) Date --Start Date 
    UNION ALL 
    SELECT Date + 1 
    FROM Mangal 
    WHERE Date + 1 < = '2015-12-31' --End date 
) 

SELECT 
    Row_Number() OVER (ORDER BY Date) as ID
    , Date  as DATE_TIME
    , YEAR (date) as YEAR_NB 
    , MONTH (date) as MONTH_NB 
    , DAY (date) as DAY_NUMBER 
    , DateName (mm, date) as MONTH_NAME 
    , LEFT ( DateName (mm, date), 3) KMONTH_NAME 
    , DateName (dw, date) as DAY_NAME 
    , LEFT (DateName (dw, date), 3) as KDAY_NAME
    , (SELECT TOP 1 FIELD
        FROM TABLEXY
        WHERE Date BETWEEN TABLEXY.DATE_FROM AND LEGISLATUR.DATE_TO
            AND LANGAGE = 'FR'
    ) as PERSONAL_FIELD
    , (SELECT TOP 1 FIELD
        FROM TABLEXY
        WHERE Date BETWEEN TABLEXY.DATE_FROM AND LEGISLATUR.DATE_TO
            AND LANGAGE = 'DE'
    ) as PERSONAL_FIELD_DE


FROM Mangal 

OPTION (MAXRECURSION 0)

score 1 · Accepted Answer · answered Jun 20 '12 at 07:39

1

SQL Server has a table containing names of Months and week days. However, they are stored as comma delimited values:

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

You might use this in your query.

answered Jun 20 '12 at 07:39

David Brabant

41,623
16
83
111

Thanks for you answer. But they are "imploded" into one string like `January,February,March,April,May,June,July,August,September,October,November,December`. Dow you know how to match the association between my query ? – j_freyre Jun 20 '12 at 08:16
I've found a article which explain how to split `syslanguages` data here: http://sqlserverdiaries.com/blog/index.php/2012/01/using-built-in-multi-language-month-and-weekday-names-for-application-lists/ I think it'll be helpful. One more time, thanks for your answer! – j_freyre Jun 20 '12 at 09:00

SQL Server multiple date languages in same query

1 Answers1