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: 
The problem, as I'm sure you're aware, is that SQL Server requires certain punctuation between the numerals before it will consider a character sequence to be a valid datetime. sp\_BlitzErik has shown one way to inject those characters. Here's another using the [FORMAT](https://learn.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql) function introduced in SQL Server 2012:

```
declare @s varchar(255) = '20180101215910';
declare @i bigint = @s;  -- implicit type conversion

select
    Raw             = @i,
    WithSeparators  = FORMAT(@i, '####-##-##T##:##:##'),
    AsDateTime      = CONVERT(datetime, FORMAT(@i, '####-##-##T##:##:##')),
    Incremented     = DATEADD(SECOND, 120, CONVERT(datetime, FORMAT(@i, '####-##-##T##:##:##')));

           Raw  WithSeparators       AsDateTime              Incremented
--------------  -------------------- ----------------------- -----------------------
20180101215910  2018-01-01T21:59:10  2018-01-01 21:59:10.000 2018-01-01 22:01:10.000

```

I have to convert to integer type because that is one that FORMAT will accept. It has to be BIGINT due to the number of digits.

Performance-wise I think you would find it very difficult to measure the difference between the two techniques.

This may have a smaller cognitive load, depending on how familiar you are with reading T-SQL.
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

```