Hur fixar jag 'Konvertering misslyckades vid konvertering av datum och / eller tid från teckensträng' -fel?

Det finns många tillfällen när datum och tider inte visas i det format du vill att det ska vara, och inte heller passar en frågeutmatning behoven hos tittarna. Det finns flera SQL Servers inbyggda funktioner för att formatera datumsträngen efter ditt behov, men för att strängen ska tolkas av SQL Server och för att undvika omvandlingsfel ska den vara i rätt format. När vi försöker konvertera datum eller tid från teckensträng uppstår ibland fel. 'Konvertering misslyckades när datum och / eller tid konverterades från teckensträng.'

Figur 1: Datum- och / eller tidsomvandlingsfel från teckensträngen

Felet som nämns ovan uppstår normalt när datumbokstaven inte är korrekt och inte kan konverteras från strängen till DateTime eller date. Det här felet beror på ett antal skäl, som vi kommer att diskutera i detalj tillsammans med lösningsuppsättningen.

Exempel 1:

Storbritannien Datum- och tidsnotering visar datumet med formatet dag-månad-år (10 januari 2015 eller 2015-01-01) som vi kan uppnå med hjälp av SQL Server inbyggd funktion ”konvertera” med formateringsstil 103.

Här i exemplet nedan kan vi se att den angivna datumsträngen har fel format. Först ger den månaden, sedan dagar och förra året som är fel och inte kan tolkas av SQL Server vilket resulterar i ett fel. Rätt format för konvertering av brittisk stildatum med '103' datumformat är 'dd / mm / åååå'.

Fel format:

Förklara @date_time_value varchar (100) = '10 / 16/2015 21:02:04 'välj CONVERT (datetime2, @date_time_value, 103) som UK_Date_Time_Style

Figur 2: Fel datumformat som resulterar i ett fel

Rätt format:

Det brittiska och franska datumformatet är 103 = “dd / mm / åååå” eller 3 = ”dd / mm / ååå”. Här är 103 och 3 datumstilar.

Förklara @date_time_value varchar (100) = '10 / 1/15 21:02:04 'välj CONVERT (datetime2, @date_time_value, 103) som Date_Time_Style

Figur 3: Rätt datumformat med “dd / mm / åååå” brittisk / fransk datumstil

Förklara @date_time_value varchar (100) = '10 / 1/15 21:02:04 'välj CONVERT (datetime2, @date_time_value, 3) som UK_Date_Time_Style

Figur 4: Rätt datumformat med “dd / mm / åå” brittisk / fransk datumstil

Exempel 2:

Ibland resulterar konvertering av sträng till datum i SQL-server i fel, inte på grund av datum- eller tidsformat som används, snarare beror det på att du försöker lagra felaktig information som inte är acceptabel för schemat.

Fel datum:

Orsaken till följande fel är bara att det inte finns något datum som ”29 februari” år 2019 eftersom det inte är ett skottår.

Förklara @date_time_value varchar (100) = '2019-02-29 21:02:04' välj cast (@date_time_value som datetime2) som date_time_value

Figur 5: Fel uppkommet eftersom 2019 inte är ett skottår, så det har inte 29 feb som datum

Rätt:

Förklara @date_time_value varchar (100) = '2019-02-28 21:02:04' välj cast (@date_time_value som datetime2) som date_time_value

Figur 6: Rätt datum

ISO 8601 Datumformat:

Även om många format finns tillgängliga för att manipulera datumvärden, kan det vara en användbarhetsfråga att välja en datetime-representation när man arbetar för en global / internationell massa. Så kulturspecifika datum / tid bokstäver bör undvikas. Om vi ​​betraktar detta datum som '03/08/2018' kommer det att tolkas på följande sätt i olika regioner i världen.

• I brittisk stil tolkas det som '8 mars 2018'
• I europeisk stil tolkas det som '3 augusti 2018'

Lyckligtvis finns det ett alternativ i det internationella datumformatet som utvecklats av ISO. Den globala standarden ISO 8601-format 'ÅÅÅÅ-MM-DDThh: mm: ss' är ett mer språkoberoende alternativ för strängbokstäver och det tar upp alla dessa frågor. Medan 'åååå' är året, är 'mm' månad och 'dd' är dag. Så datumet '8 mars 2018' i internationellt ISO-format skrivs som '2018-03-08'. Således är ISO-format det bästa valet för datumrepresentation.

Förklara @date_time_value varchar (100) = '2019-03-28 21:02:04' välj konvertera (datetime2, @ date_time_value, 126) som [åååå-mm-ddThh: mi: ss.mmm]

Bild 7: Internationell standard ISO 8601 datumformat

Rekommendationer:

Förhoppningsvis kommer den här artikeln att hjälpa till att lindra den förvirring jag ofta har sett i samhället om värden för datum och tid. Det rekommenderas dock att aldrig lagra datum i texttyp (varchar, char, nvarchar, nchar eller text) Spara alltid datumvärde i kolumnerna DATE, DATETIME och helst DATETIME2 (ger mer precision) och lämna datuminformationsformateringen till användargränssnittsskiktet istället för att hämtas från databasen.