SQL SET DATEFORMAT

The SQL SET DATEFORMAT Functions will set the Order of the Date: Month, Year, and Day. This Statement supports formats like dmy, mdy, ymd, ydm, and dym where d = Date, m = month, and y = Year.

The basic syntax of the SQL DATEFORMAT is as shown below:

SET DATEFORMAT DateFormat_Name

SQL SET DATEFORMAT Example

In this example, we will show how the SQL FORMAT will affect the Date. Remember, SET LANGUAGE will implicitly change the date format based on the Language you select. But, it will override that format.

-- Setting as date/Month/Year
SET DATEFORMAT dmy
DECLARE @dt DATETIME2 = '01-02-2016 12:03:28.000'
SELECT @dt AS 'date from DMY Format'

DECLARE @dt1 DATETIME2 = '02/05/2016 12:03:28.000'
SELECT @dt1 AS 'date from DMY Format'

-- Setting as Month/date/year
SET DATEFORMAT mdy
DECLARE @dt2 DATETIME2 = '01-02-2016 12:03:28.000'
SELECT @dt2 AS 'date from MDY Format'