Hoe de fout 'Conversie mislukt bij het converteren van datum en/of tijd van tekenreeks' op te lossen?

Er zijn veel gevallen waarin datums en tijden niet worden weergegeven in het formaat dat u wilt, en de uitvoer van een zoekopdracht past ook niet bij de behoeften van de kijkers. Er zijn verschillende ingebouwde functies van SQL Server om de datumreeks naar uw behoefte op te maken, maar om de tekenreeks door SQL Server te laten interpreteren en conversiefouten te voorkomen, moet deze de juiste indeling hebben. Wanneer we de datum of tijd van de tekenreeks proberen om te zetten, treedt soms de volgende fout op. "Conversie mislukt bij het converteren van datum en/of tijd uit tekenreeks."

De hierboven genoemde fout treedt normaal gesproken op wanneer de letterlijke datum niet juist is en niet kan worden geconverteerd van de tekenreeks naar DateTime of date. Deze fout is het gevolg van een aantal redenen, die we samen met de oplossing in detail zullen bespreken.

Voorbeeld 1:

Verenigd Koninkrijk Datum- en tijdnotatie geven de datum weer met behulp van de dag-maand-jaarnotatie (10 januari 2015 of 1-10-2015) die we kunnen bereiken met de ingebouwde functie "converteren" van SQL Server met opmaakstijl 103.

Hier in het onderstaande voorbeeld kunnen we zien dat de opgegeven datumstring de verkeerde indeling heeft. Ten eerste geeft het de maand, dan de dagen en het afgelopen jaar, wat verkeerd is en niet kan worden geïnterpreteerd door SQL Server, wat resulteert in een fout. Het juiste formaat voor datumconversie in Britse stijl met behulp van "103" datumstijl is "dd/mm/jjjj".

Verkeerd formaat:

Declareer @date_time_value varchar(100)= '10/16/2015 21:02:04' selecteer CONVERT(datetime2, @date_time_value, 103) als UK_Date_Time_Style

Juiste formaat:

Het Britse en Franse datumformaat is 103 = “dd/mm/jjjj” of 3=”dd/mm/jj”. Hier zijn 103 en 3 datumstijlen.

Declareer @date_time_value varchar(100)= '10/1/15 21:02:04' selecteer CONVERT(datetime2, @date_time_value, 103) als Date_Time_Style

Declareer @date_time_value varchar(100)= '10/1/15 21:02:04' selecteer CONVERT(datetime2, @date_time_value, 3) als UK_Date_Time_Style

Voorbeeld 2:

Soms resulteert string-to-date-conversie in SQL-server in een fout, niet vanwege de gebruikte datum- of tijdnotaties, maar omdat u probeert onjuiste informatie op te slaan die niet acceptabel is voor het schema.

Verkeerde datum:

De reden voor de volgende fout is louter dat er in het jaar 2019 niet zo'n datum is als "29 februari", omdat het geen schrikkeljaar is.

Declare @date_time_value varchar(100)= '2019-02-29 21:02:04' selecteer cast(@date_time_value als datetime2) als date_time_value

Juiste:

Declare @date_time_value varchar(100)= '2019-02-28 21:02:04' selecteer cast(@date_time_value als datetime2) als date_time_value

Datumformaat volgens ISO 8601:

Hoewel er talloze formaten beschikbaar zijn voor het manipuleren van datumwaarden, kan het bij het werken voor een globale/internationale massa een bruikbaarheidsprobleem zijn om een datetime-representatie te kiezen. Dus cultuurspecifieke datum-/tijdletters moeten worden vermeden. Als we deze datum als "03/08/2018" beschouwen, zal deze in verschillende regio's van de wereld op verschillende manieren worden geïnterpreteerd.

In Britse stijl wordt het geïnterpreteerd als "8 maart 2018"
In Europese stijl wordt het geïnterpreteerd als "3 augustus 2018"

Gelukkig is er één alternatief in het internationale datumformaat dat door ISO is ontwikkeld. De wereldwijde standaard ISO 8601-indeling "JJJJ-MM-DDTuu: mm: ss" is een taalonafhankelijke optie voor letterlijke tekenreeksen en lost al deze problemen op. Terwijl "jjjj" het jaar is, is "mm" maand en is "dd" dag. Dus de datum "8 maart 2018" in het internationale ISO-formaat wordt geschreven als "2018-03-08". Het ISO-formaat is dus de beste keuze voor dataweergave.

Declareer @date_time_value varchar(100)= '2019-03-28 21:02:04' selecteer convert(datetime2,@date_time_value,126) als [jjjj-mm-ddThh:mi:ss.mmm]

Aanbevelingen:

Hopelijk zal dit artikel helpen om de verwarring die ik vaak in de gemeenschap heb gezien over datum/tijd-waarden te verlichten. Het wordt echter aanbevolen om datums nooit in teksttype (varchar, char, nvarchar, nchar of tekst) op te slaan. Bewaar de datumwaarde altijd in DATE, DATETIME en bij voorkeur DATETIME2 (biedt meer precisie) typekolommen en laat de opmaak van de datuminformatie naar de gebruikersinterfacelaag in plaats van te worden opgehaald uit de database.