Analyzing the causes of errors in SQL date conversion in SQL Server

Developers sometimes use SQL like the following to convert strings to date-time types, and at first glance, there is nothing wrong with the way SQL is written. But such a SQL is sometimes a ticking time bomb, problems may occur at any time (), the following is a brief summary of this situation.

SELECT CONVERT (DATETIME, '2020-01-13 6 46 purl 42')

If you change the default language of the login name of the connection database to Aribc, and then execute the above SQL statement, you will encounter an error. Why?

Why did the date conversion of the above SQL go wrong? In fact, after the login name changes the default language, the date_format corresponding to the session changes from mdy to dmy, so the above conversion reports an error. Sometimes it does not report an error, but it may be converted to an error date, resulting in a logic error. Instead, this is a bad hidden error. By the time you find out, you may have generated a lot of wrong data.

SELECT session_id, program_name, client_interface_name, language, date_formatFROM sys.dm_exec_sessionsWHERE session_id = 53

For the default date_format for different languages, you can view it using the following command:

Sp_helplanguage 'us_english'

Alternatively, if the current session uses the SET command to modify DATEFORMAT, you will also encounter this error, as shown below:

SET DATEFORMAT DMY;GOSELECT CONVERT (DATETIME, '2020-01-13 6 46 purl 42')

This situation is more complicated, it is possible that DATEFORMAT is set in a certain SQL, resulting in a change in the format of the date at the end of the entire session. Therefore, the "robustness" of the above SQL is relatively poor, and it is necessary to avoid writing such a SQL in peacetime. If you use such a SQL, no matter the default language of the session has changed, or the DATEFORMAT of the current session has changed, there will be no errors or logic errors.

SELECT CONVERT (DATETIME,'2020-01-13 6-46-40).

Usually encounter this kind of date conversion, you must clearly specify the conversion format, so that it is not affected by the DATEFORMAT changes of the session, write robust and reliable SQL statements, the following two simple SQL nuances, you can also judge whether a person has the consciousness of writing robust SQL!

SELECT CONVERT (DATETIME, '2020-01-13 6); SELECT CONVERT (DATETIME,' 2020-01-13 6)

Summary

The above is the reason for the error in the analysis of SQL date conversion in SQL Server introduced by the editor. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply to you in time. Thank you very much for your support to the website!

If you think this article is helpful to you, you are welcome to reprint it, please indicate the source, thank you!