SET DATEFORMAT (Transact-SQL)
Sets the order of the dateparts (month/day/year) for entering datetime or smalldatetime data.
.gif "Topic link icon")
Transact-SQL Syntax Conventions
Syntax
SET DATEFORMAT { format | @format_var }
Arguments
- 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.
Remarks
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.
Permissions
Requires membership in the public role.
Examples
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
See Also
Reference
CREATE FUNCTION (Transact-SQL)
Data Types (Transact-SQL)
Date and Time (Transact-SQL)
SET (Transact-SQL)