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)

Answer 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.