Export (0) Print
Expand All
Expand Minimize

SET DATEFORMAT (Transact-SQL)

Sets the order of the dateparts (month/day/year) for entering datetime or smalldatetime data.

Topic link icon Transact-SQL Syntax Conventions


SET DATEFORMAT { format | @format_var } 

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

Community Additions

ADD
Show:
© 2014 Microsoft