Developers sometimes use SQL like the following to convert strings to date time types. At first glance, there is no problem with such SQL. However, such SQL is actually a time bomb, which may cause problems at any time (), the following is a brief summary of this situation.
Why is the date conversion error in SQL above? In fact, after the login name changes the default language, the corresponding date of the session is_ The format has changed from mdy to DMY. Therefore, errors are reported in the above conversion. Sometimes no error is reported. However, it may be converted into an incorrect date, resulting in a logical error. This is a bad hidden error. By the time you find out, a lot of bad data may have been generated.
About the default date for different languages_ Format, which can be viewed with the following command:
On the other hand, if the current session uses the set command to modify dateformat, this error will also be encountered, as shown below:
This situation is more complicated. It may be set in a certain SQL sectionDATEFORMATThe format of the date after the entire session has changed. So the above SQL“Robustness”If you use such SQL, whether the default language of the session has changed or the dateformat of the current session has changed, there will be no error or logical error.
SELECT CONVERT(DATETIME,‘2020-01-13 6:46:42’, 120)。
When you encounter this kind of date conversion, you must specify the conversion format clearly, so that it is not affected by the change of dateformat in the session, and write robust and reliable SQL statements. The following two simple SQL differences can also distinguish whether a person has the awareness of writing robust SQL!
SELECT CONVERT(DATETIME, ‘2020-01-13 6:46:42’);
SELECT CONVERT(DATETIME, ‘2020-01-13 6:46:42’, 120)