Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
1 2 3 4 5 6 7 | 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.
1 2 3 4 5 6 7 | 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:
1 2 3 4 5 6 | SET DATEFORMAT dmy; SET DATEFORMAT mdy; SET DATEFORMAT ydm; SET DATEFORMAT ymd; SET DATEFORMAT dym; SET DATEFORMAT myd; |
