SET DATEFORMAT is responsible for how character strings are interpreted when converted to datetime. It's not resposible for the way db stores datetime values, and not responsible for how these values are displayed in ssms or somewhere else.

Using it in such way may lead to error. For example:

--let's say we have some important date hardcoded in out query

set dateformat ymd;

declare @SomeImportantDate datetime = '1999-02-01';

select @SomeImportantDate; -- SSMS displays it like '1999-02-01 00:00:00.000'

select datename(mm,@SomeImportantDate) + ' ' + datename(dd,@SomeImportantDate); -- and it is: February 1

go

set dateformat ydm;

declare @SomeImportantDate datetime = '1999-02-01'; --the same initialization

/*

Now SSMS displays it like '1999-01-02 00:00:00.000', so we may think that we

changed display style somehow...

*/

select @SomeImportantDate;

-- but lets check - is it still the same date

select datename(mm,@SomeImportantDate) + ' ' + datename(dd,@SomeImportantDate); -- and it is: January 2

/*

No, it is another date, and the query logic might be corrupted,

we just influenced on how @SomeImportantDate variable was initialized

That's why to avoid such thing it's recommended to use independent formats for datetime like:

'19990201' or (with time portion) '1999-01-02T00:00:00.000'

*/

go

So, as GSquared said, it is client responsibility to display date in specific format. When you run query in SSMS and see smth like - '1999-01-02 00:00:00.000' - it is not sql server, it is how SSMS displays dates.