Statement on different SQL Server with different languages

Question

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

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]

Can I use just use SET LANGUAGE german; at the beginning of the script and this setting is only temporary for the statement and does not change the language permanently? Or is this not a good way to go? See used statement 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's best to use the (slightly adapted) ISO-8601 format for SQL Server dates-as-string-literals.

If you need to use DATETIME (which should be phased out as of SQL Server 2008 - use DATE if you need only date, DATETIME2(n) if you need both dates & 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 only need the DATE (no time), use the DATE datatype and then use this format:

YYYYMMDD

This is guaranteed to work always and without having to switch around date and language settings just to make your INSERT statements work ....

Thus 20170401 will always be interpreted as "April 1, 2017"