SET DATEFORMAT in SQL Server(TSQL)

dbtut

6 years ago

You can specify the date format that the corresponding session accepts with “SET DATEFORMAT”. You may need this command if you want to assign a string data to a variable of type datetime.

Let’s make an example for a better understanding of SET DATEFORMAT.

The following code block will return a result without error. Because string data is compatible with the format dmy.

SET DATEFORMAT dmy

DECLARE @date datetime

SELECT @date='31-12-2018'

SELECT @date

SET DATEFORMAT dmy

DECLARE @date datetime

SELECT @date='31-12-2018'

SELECT @date

But the below code block will throw the below error. Because string data is not compatible with mdy format.

SET DATEFORMAT mdy

DECLARE @date datetime

SELECT @date='31-12-2018'

SELECT @date

SET DATEFORMAT mdy

DECLARE @date datetime

SELECT @date='31-12-2018'

SELECT @date

Msg 242, Level 16, State 3, Line 9

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

You can use SET DATEFORMAT in following ways:

SET DATEFORMAT dmy;
SET DATEFORMAT mdy;
SET DATEFORMAT ydm;
SET DATEFORMAT ymd;
SET DATEFORMAT dym;
SET DATEFORMAT myd;

SET DATEFORMAT dmy;

SET DATEFORMAT mdy;

SET DATEFORMAT ydm;

SET DATEFORMAT ymd;

SET DATEFORMAT dym;

SET DATEFORMAT myd;