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 , then it gives an error of conversion of a varchar data type to datetime .
5,553 total views, 7 views today