SET DATEFORMAT (T-SQL)

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

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 has no effect on the display of date values.

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

Permissions

SET DATEFORMAT permissions default to all users.

Examples

This example uses different date formats to handle date strings in different formats.

SET DATEFORMAT mdy

GO

DECLARE @datevar datetime

SET @datevar = '12/31/98'

SELECT @datevar

GO

SET DATEFORMAT ydm

GO

DECLARE @datevar datetime

SET @datevar = '98/31/12'

SELECT @datevar

GO

SET DATEFORMAT ymd

GO

DECLARE @datevar datetime

SET @datevar = '98/12/31'

SELECT @datevar

GO

See Also