I have already explained you regarding RANK(), DENSE_RANK(), ROW_NUMBER() and Aggregate functions in sql server. Today I am going to explain regarding how do we change default date format in sql server.

There are the requirements where we will be required to change default date format in sql server. The default date format of SQL is mdy(U.S English). Now to change sql server default date format from “mdy”(mm/dd/yyyy) to “dmy”(dd/mm/yyyy),we have to use SET DATEFORMAT command. Before changing the default date time format of sql server lets go through the way to know what format of date time is currently in use.

Now to know the  date format which is currently in use, use below command.

DBCC UserOptions

In the result go over through the date format row to know the current format of sql server.

Now to change the date format of sql server from “mdy” to “dmy” use below command.

SET DATEFORMAT dmy

Now once again run the user option command to check new date format of sql server. It’s changed to dmy which is dd/mm/yyyy.

Below are the valid date format which we can use with SET DATEFORMAT command.

• mdy
• dmy
• ymd
• ydm
• myd
• dym

I found very good example what is the exact use of DATEFORMAT in SQL Server from MSDN, which is as below:

-- Set date format to day/month/year.
-- To Change default date format in sql server
SET DATEFORMAT dmy;
GO
DECLARE @datevar datetime2 = '31/12/2008 09:01:01.1234567';
SELECT @datevar;
GO
-- Result: 2008-12-31 09:01:01.123
SET DATEFORMAT dmy;
GO
DECLARE @datevar datetime2 = '12/31/2008 09:01:01.1234567';
SELECT @datevar;
GO
-- Result: Msg 241: Conversion failed when converting date and/or time -- from character string.

GO