Application in different SQL Server with different languages

Question

I insert the SQL statement into the table with two dates, but I use it on different machines with different SQL Server and install the language. Sometimes "2017-01-04 00:00:00" is April 01, 2017, sometimes it is January 4, 2017. I want April 01th.

INSERT INTO [table] ([id], [value], [datefrom], [dateto]) SELECT NEWID(), 3.5, '2017-01-04 00:00:00', '2027-01-05 00:00:00' FROM [settings] 

Is it possible to use only SET LANGUAGE german; at the beginning of the script, and this parameter is temporary only for the instruction and does not change the language forever? Or is this not the best way? See the instructions below. Thanks in advance.

 SET LANGUAGE german; INSERT INTO [table]([id], [value], [datefrom], [dateto]) SELECT NEWID(), 3.5, '2017-01-04 00:00:00', '2027-01-05 00:00:00' FROM [settings] 

Answer 1

If you need to support multiple language settings, it is best to use the (slightly adapted) ISO-8601 format for dates in SQL Server format.

If you need to use DATETIME (which should be phased out with SQL Server 2008), use DATE if you only need a date, DATETIME2(n) if you need both dates and time), use one of two possible formats:

 YYYYMMDD HH:MM:SS // **NO** dashes for the date portion! 

or

 YYYY-MM-DDTHH:MM:SS // delimiter "T" between date and time parts 

Preferably, if you really need a DATE (no time), use the DATE data type and then use this format:

 YYYYMMDD 

It is guaranteed that it will work always and without the need to change the date and language parameters to make your INSERT work.

Therefore, 20170401 will always be interpreted as “April 1, 2017”

Answer 2

For date and time, you can use the literal (ODBC) language, {d 'YYYYMMDD'} for the date, and {ts'YYYY-MM-DD hh:mm:ss[.nnn]'} .

So

 {ts '2017-04-01 00:00:00'} 

must work.

Answer 3

SET LANGUAGE command Works at the session level, so this will solve your problem, see MDSN Article in the command for specifics