• NBSteve - Tuesday, November 14, 2017 10:21 AM

    It feels inconsistent because it isn't quite accurate.  SET LANGUAGE doesn't change the value of the date, it changes the way string-date conversions are handled.  So when "DECLARE @RunDate DATE = '10/02/17' " is executed, it's the SET LANGUAGE value at that time that determines how it's interpreted.  Subsequent SET LANGUAGE calls will change how future string-date conversions are handled, even within the existing batch, but because @RunDate is already stored as a date, there are no more conversions being done here.  Compare the results of the above code to this, where an implicit string-date conversion happens in each line:  

    SET LANGUAGE Italian
    SELECT DATENAME(dd, '10/02/17')
    SELECT DATENAME(MONTH, '10/02/17')
    SET LANGUAGE US_English
    SELECT DATENAME(dd, '10/02/17')
    SELECT DATENAME(MONTH, '10/02/17')

    I completely overlooked that the date conversion was already done before. The "The SET LANGUAGE for the SELECT doesn't change this until the end of the batch." put me on a wrong track and made me believe that the change of the date format was done only at the end of the batch. It is in fact wrong, because the data format is directly changed when the SET LANGUAGE is called. Reading back the explanation I guess that the question had two SET LANGUAGE commands in there, because it is talking about "The SET LANGUAGE for the variable declaration" and that is not there.