SQL Server – Changing Date Order System Wide

You can try the ALTER SESSION

ALTER SESSION SET NLS_DATE_FORMAT = '[date_format]';

date_formats

MM  Numeric month (e.g., 07)
MON     Abbreviated month name (e.g., JUL)
MONTH   Full month name (e.g., JULY)
DD  Day of month (e.g., 24)
DY  Abbreviated name of day (e.g., FRI)
YYYY    4-digit year (e.g., 1998)
YY  Last 2 digits of the year (e.g., 98)
RR  Like YY, but the two digits are ``rounded'' to a year in the range 1950 to 2049. Thus, 06 is considered 2006 instead of 1906
AM (or PM)  Meridian indicator
HH  Hour of day (1-12)
HH24    Hour of day (0-23)
MI  Minute (0-59)
SS  Second (0-59)

TO_CHAR(datum_column, date_format) is the best method to avoid headpains check Justin Cave's answer

In SQL Server 2012, date takes 3 bytes while datetime takes 8 bytes. If your table has millions of millions of rows, then yes, then the IO and logical page size should be reduced and your performance will be quicker.

First though i'd suggest to try dumping the table into a testing table, convert the column, apply your indexes and benchmark using real life workloads against this table using the original as a baseline.