The SQL DATEFORMAT is one of the Set Function, which will set the Order of the date : Month, Year, Day. This Statement supports formats like dmy, mdy, ymd, ydm, and dym where d = Date, m = month, and y = Year. This article will show you, How to use this SET DATEFORMAT in SQL Server with example.
SQL DATEFORMAT Syntax
The basic syntax of the DATEFORMAT in SQL Server is as shown below:
|
1 2 3 4 |
SET DATEFORMAT DateFormat_Name -- For example, SET DATEFORMAT dmy; |
SQL DATEFORMAT Example
In this example we will show you, How the SET DATEFORMAT in SQL Server will affect the date. Remember, SET LANGUAGE will implicitly change the date format based on the Language you select. But, SET DATEFORMAT will override that format.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
-- Setting the Format as date/Month/Year SET DATEFORMAT dmy DECLARE @dt DATETIME2 = '01-02-2016 12:03:28.000' SELECT @dt AS 'date from DMY Format' DECLARE @dt1 DATETIME2 = '02/05/2016 12:03:28.000' SELECT @dt1 AS 'date from DMY Format' -- Setting the format as Month/date/year SET DATEFORMAT mdy DECLARE @dt2 DATETIME2 = '01-02-2016 12:03:28.000' SELECT @dt2 AS 'date from MDY Format' |
OUTPUT
Thank You for Visiting Our Blog
Share your Feedback, or Code!!