How to change the Date Format in SQL Server?

To view the current format use the below query.
----------------------------------------------
select name ,alias, dateformat
from sys.syslanguages
where langid =
(select value from master..sysconfigures
where comment = 'default language')


To Change Date Format as DMY
-----------------------------
sp_configure 'default language', 23
reconfigure with override

To reset DMY to MDY replace 23 to 0

Note: It will affect only for the particular User login.