SET DATEFORMAT in SQL Server(TSQL)

 

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.

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

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:

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *