Udostępnij za pośrednictwem


SET DATEFORMAT (Transact-SQL)

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

Topic link iconTransact-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)

Other Resources

Using Options in SQL Server

Help and Information

Getting SQL Server 2005 Assistance