Hoe de ‘Conversie mislukt bij het converteren van datum en / of tijd van tekenreeks’ te verhelpen?

Er zijn veel gevallen waarin datums en tijden niet worden weergegeven in het formaat dat u wilt, en evenmin voldoet een query-uitvoer aan de behoeften van de kijkers. Er zijn verschillende ingebouwde functies van SQL Server om de datatekenreeks op te maken volgens uw behoefte, maar om de tekenreeks door SQL Server te laten interpreteren en om conversiefouten te voorkomen, moet deze de juiste indeling hebben. Wanneer we de datum of tijd proberen te converteren van de tekenreeks, doet zich soms de volgende fout voor. 'Conversie is mislukt bij het converteren van datum en / of tijd uit tekenreeks.'

Figuur 1: Datum- en / of tijdconversiefout van de tekenreeks

De hierboven genoemde fout treedt normaal gesproken op als 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 oplossingsset in detail zullen bespreken.

Voorbeeld 1:

Verenigd Koninkrijk Datum- en tijdnotatie geven de datum weer met behulp van het dag-maand-jaar-formaat (10 januari 2015 of 10/1/2015) dat we kunnen bereiken met de ingebouwde SQL Server-functie 'convert' -functie 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 laatste jaar aan, wat verkeerd is en niet kan worden geïnterpreteerd door SQL Server, wat resulteert in een fout. Het juiste formaat voor dataconversie in Britse stijl met behulp van de datumstijl '103' is 'dd / mm / jjjj'.

Verkeerd formaat:

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

Figuur 2: Verkeerde datumnotatie resulteert in een fout

Correct formaat:

De Britse en Franse datumnotatie is 103 = 'dd / mm / jjjj' of 3 = 'dd / mm / jj'. Hier zijn 103 en 3 datumstijlen.

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

Figuur 3: Correcte datumnotatie met 'dd / mm / jjjj' Britse / Franse datumstijl

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

Figuur 4: Correcte datumnotatie met 'dd / mm / jj' Britse / Franse datumstijl

Voorbeeld 2:

Soms resulteert conversie van tekenreeks-tot-datum in SQL Server in fouten, 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 geen datum als '29 februari' is, 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

Figuur 5: Fout opgetreden omdat 2019 geen schrikkeljaar is, dus het heeft geen 29 februari als datum

Correcte:

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

Figuur 6: juiste datum

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-weergave te kiezen. Dus cultuurspecifieke datum- / tijdletters moeten worden vermeden. Als we deze datum '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 door ISO ontwikkelde internationale datumnotatie. 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 'dd' dag. Dus de datum '8 maart 2018' in internationaal ISO-formaat wordt geschreven als '2018-03-08'. Het ISO-formaat is dus de beste keuze voor datumweergave.

Declare @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]

Afbeelding 7: Datumnotatie volgens internationale standaard ISO 8601

Aanbevelingen:

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