Home Forums Article Discussions Article Discussions by Author Discuss Content Posted by Steve Jones Interesting Datenames RE: Interesting Datenames

  • November 14, 2017 at 10:21 am

    #1968038

    Mighty - Tuesday, November 14, 2017 3:16 AM

    Something that might not be clear is that only the date format is not changed until the end of the batch. What the DATENAME returns is directly affected.

    DECLARE @RunDate DATE = '10/02/17'
    SET LANGUAGE Italian
    SELECT DATENAME(dd, @RunDate)
    SELECT DATENAME(MONTH, @RunDate)
    SET LANGUAGE US_English
    SELECT DATENAME(dd, @RunDate)
    SELECT DATENAME(MONTH, @RunDate)

    For me that feels a bit inconsistent.

    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')