SQL SERVER SET DATEFORMAT

SQL Server SET DATEFORMAT is used to set the order of the month, day, and year date parts for interpreting date character strings.

These strings are of type date, smalldatetime, datetime, datetime2, or datetimeoffset.  The options for SET DATEFORMAT are mdy, dmy, ymd, ydm, myd, or dym. The default SET DATEFORMAT is mdy

The DATEFORMAT does not support the ydm for date, datetime2, and datetimeoffset data types.

SYNTAX

SET DATEFORMAT { format | @format_var }

format | @format_var

Is the order of the date parts.Valid parameters are mdy, dmy, ymd, ydm, myd, and dym.

Lets look at an example of SET DATEFORMAT in SQL Server.

Following example demonstrate how date character string can be converted to proper date format using SET DATEFORMAT command.

CREATE TABLE SampleDates (formatDate Datetime)

SET DATEFORMAT MDY
INSERT INTO SampleDates
VALUES ('09/30/2019 00:00:00.000')

SET DATEFORMAT DMY
INSERT INTO SampleDates
VALUES ('30/09/2019 00:00:00.000')

SET DATEFORMAT YMD
INSERT INTO SampleDates
VALUES ('2019/09/30 00:00:00.000')

SET DATEFORMAT YDM
INSERT INTO SampleDates
VALUES ('2019/30/09 00:00:00.000')

SET DATEFORMAT MYD
INSERT INTO SampleDates
VALUES ('09/2019/30 00:00:00.000')

SET DATEFORMAT DYM
INSERT INTO SampleDates
VALUES ('30/2019/09 00:00:00.000')
 

As you can see, the different different format of date character string are inserted into table successfully without giving any conversion error.

SELECT * FROM SampleDates

 

Lets see, If you do not use SET DATEFORMAT  for last insert statement .

INSERT INTO SampleDates
VALUES ('30/2019/09 00:00:00.000')

You can see, it gives an error of conversion of a varchar data type to datetime.

SQL Format Function