SET DATEFORMAT (Transact-SQL)

format | @format_var

Is the order of the dateparts. Can be either Unicode or DBCS converted to Unicode. Valid parameters include mdy, dmy, ymd, ydm, myd, and dym. The U.S. English default is mdy.

This setting is used only in the interpretation of character strings as they are converted to date values. It does not affect the display of date values.

The setting of SET DATEFORMAT is set at execute or run time and not at parse time.

SET DATEFORMAT overrides the implicit date format setting of SET LANGUAGE.

Requires membership in the public role.

The following example uses different date formats to handle date strings in different formats.

-- Set date format to month, day, year.
SET DATEFORMAT mdy;
GO
DECLARE @datevar DATETIME;
SET @datevar = '12/31/1998';
SELECT @datevar AS DateVar;
GO
-- Result: 1998-12-31 00:00:00.000

-- Set date format to year, day, month.
SET DATEFORMAT ydm;
GO
DECLARE @datevar DATETIME;
SET @datevar = '1998/31/12';
SELECT @datevar AS DateVar;
GO
-- Result: 1998-12-31 00:00:00.000

-- Set date format to year, month, day.
SET DATEFORMAT ymd;
GO
DECLARE @datevar DATETIME;
SET @datevar = '1998/12/31';
SELECT @datevar AS DateVar;
GO
-- Result: 1998-12-31 00:00:00.000

Reference

Other Resources

Help and Information