Change date format using SET DATEFORMAT in sql server

Here i will show how you can change date format of sql server.

SET DATEFORMAT <FORMAT>

Sets the order of dateparts (month/day/year) for entering datetime or smalldate data,valid parameters include mdy,dmy,ymd,ydm,myd, and dym.

Note:

this method allows you  use a date format for dates sent to sql server d/m/y, but it is a connection dependent.

If a new connection is made to sql server is stopped or restarted, the date format goes back to its default setting.

SET DATEFORMAT dmy

insert into table1(Datecolumn) values ('19/12/2100');

SET DATEFORMAT mdy

insert into table1(datecolumn) values ('12/19/2100');