The error mentioned above normally arises when the date literal is not proper and cannot be converted from the string into DateTime or date. This error results due to a number of reasons, which we will discuss in detail along with the solution set.
Example 1:
United Kingdom Date and time notation display the date using the day-month-year format (January 10, 2015 or 10/1/2015) which we can achieve using SQL Server built_in feature “convert” function with formatting style 103. Here in the example below we can see that the provided date string is in the wrong format. First, it is providing the month, then days and at last year which is wrong and cannot be interpreted by SQL Server which results in an error. The correct format for UK style date conversion using “103” date style is “dd/mm/yyyy”.
Wrong Format:
Correct Format:
The British and French date format is 103 = “dd/mm/yyyy” or 3=” dd/mm/yy”. Here 103 and 3 are date styles.
Example 2:
Sometimes string to date conversion in SQL server results in error, not because of the date or time formats used, rather it’s because you’re attempting to store incorrect information that’s not acceptable to the scheme.
Wrong Date:
The reason for the following error is mere that in the year 2019 there is no such date as “29 February” because it is not a leap year.
Correct One:
ISO 8601 Date Format:
Although numerous formats are available for manipulating date values, when working for a global/ international mass, it can be a usability issue to choose a datetime representation. So culture-specific date/time literals should be avoided. If we consider this date “03/08/2018”, it will be interpreted in following different ways in different regions of the world.
In UK style it is interpreted as “8th of March 2018”In European style it is interpreted as “3rd of August 2018”
Luckily, there is one alternative in the international date format developed by ISO. The global standard ISO 8601 format “YYYY-MM-DDThh: mm: ss” is a more language-independent option for string literals and it addresses all these issues. Whereas, “yyyy” is the year, “mm” is month and “dd” is day. So the date “8th of March 2018”in international ISO format is written as “2018-03-08”. Thus ISO format is the best choice for date representation.
Recommendations:
Hopefully, this article will assist to relieve the confusion I have frequently seen in the community about date/time values. However, it is recommended that never store dates in text-type (varchar, char, nvarchar, nchar,or text) Always store date value in DATE, DATETIME and preferably DATETIME2 (provides more precision) type columns and, leave the date information formatting to the user interface layer instead of being retrieved from the database.
Find and Replace String/Character/Symbol with a New Line in Notepad++Why Converting YouTube to 320kbps MP3 is a Waste of TimeHow to Fix “failed to create KVP sessions string error 0x8007007a"Converting Batch Files .BAT to Executables .EXE
