Question: The following will return the day in Italian: 

```
SET lc_time_names = 'it_IT';
select date_format('2018/01/01','%W') as day_italian;

```

However I need to convert or format the date into multiple languages, so it would return me another column in English, Japanese, so on...

My problem is that I have to set the locale BEFORE running the select command.

Answer: 
Let me know how this works for you.

```
CREATE TABLE #t (callstarttime VARCHAR(255), callduration INT);
INSERT #t ( callstarttime, callduration )
SELECT *
FROM (
VALUES('20180101215910', 120),('20180101215710', 220)
) AS x (callstarttime, callduration);

WITH munge AS (
SELECT *, 
    LEFT(callstarttime, 8) AS d,
    STUFF(STUFF(SUBSTRING(callstarttime, 9, LEN(callstarttime)) , 3, 0, ':'), 6, 0, ':') AS t
FROM #t AS t
)
SELECT *, 
       TRY_CONVERT(DATETIME, d + ' ' + t),
       DATEADD(SECOND, munge.callduration, TRY_CONVERT(DATETIME, d + ' ' + t))
FROM munge

```
If we can assume *callstarttime* is a standard format and you have permission in MSDB, the agent\_datetime function should get you what you're looking for.

```
CREATE TABLE #mytable (callstarttime varchar(255),callduration int)

INSERT INTO #mytable VALUES ('20180101215910',120),('20180101215710',220)

SELECT 
    msdb.dbo.agent_datetime(SUBSTRING(callstarttime,1,8), SUBSTRING(callstarttime,9,6)) AS [CallStartTime],
    DATEADD(SECOND,callduration,msdb.dbo.agent_datetime(SUBSTRING(callstarttime,1,8), SUBSTRING(callstarttime,9,6))) AS [CallEndTime]
FROM #mytable

DROP TABLE #mytable

```