Ako opraviť chybu „Konverzia zlyhala pri prevode dátumu a / alebo času z reťazca znakov“?

Existuje veľa prípadov, keď sa dátumy a časy nezobrazia vo formáte, aký chcete, ani výstup dotazu nezodpovedá potrebám divákov. Existuje niekoľko zabudovaných funkcií servera SQL Server na formátovanie reťazca dátumu podľa vašich potrieb, ale reťazec musí byť interpretovaný serverom SQL Server a aby sa zabránilo chybám pri prevode, mal by byť v správnom formáte. Keď sa pokúsime previesť dátum alebo čas z reťazca znakov, niekedy nastane nasledujúca chyba. „Konverzia zlyhala pri prevode dátumu a / alebo času z reťazca znakov.“

Obrázok 1: Chyba prevodu dátumu a / alebo času z reťazca znakov

Vyššie uvedená chyba zvyčajne nastane, keď dátumový literál nie je správny a nemožno ho previesť z reťazca na DateTime alebo date. Táto chyba je výsledkom z mnohých dôvodov, ktorým sa budeme podrobne venovať spolu so sadou riešení.

Príklad 1:

Veľká Británia Notácia dátumu a času zobrazuje dátum pomocou formátu deň-mesiac-rok (10. januára 2015 alebo 1.10.2015), ktorého môžeme dosiahnuť pomocou funkcie zabudovaného servera SQL Server „prevádzať“ štýlom formátovania 103.

Tu v príklade nižšie vidíme, že poskytnutý reťazec dátumu je v nesprávnom formáte. Najprv poskytuje mesiac, potom dni a posledný rok, čo je nesprávne a nemôže byť interpretované serverom SQL Server, čo vedie k chybe. Správny formát pre prevod dátumu v štýle Veľkej Británie používajúci štýl dátumu „103“ je „dd / mm / rrrr“.

Zlý formát:

Deklarovať @date_time_value varchar (100) = '16.10.2015 21:02:04' vyberte CONVERT (datetime2, @date_time_value, 103) ako UK_Date_Time_Style

Obrázok 2: Nesprávny formát dátumu, ktorý vedie k chybe

Správny formát:

Britský a francúzsky formát dátumu je 103 = „dd / mm / rrrr“ alebo 3 = „dd / mm / rrr“. Tu 103 a 3 sú štýly dátumu.

Deklarovať @date_time_value varchar (100) = '10 / 15/15 21:02:04 'vyberte CONVERT (datetime2, @date_time_value, 103) ako Date_Time_Style

Obrázok 3: Správny formát dátumu v britskom / francúzskom štýle dátumu „dd / mm / rrrr“

Deklarovať @date_time_value varchar (100) = '10 / 15/15 21:02:04 'vyberte CONVERT (datetime2, @date_time_value, 3) ako UK_Date_Time_Style

Obrázok 4: Správny formát dátumu v britskom / francúzskom štýle dátumu „dd / mm / rr“

Príklad 2:

Konverzia reťazca na dátum na serveri SQL niekedy vedie k chybe, nie kvôli použitým formátom dátumu alebo času, skôr preto, že sa pokúšate uložiť nesprávne informácie, ktoré nie sú pre túto schému prijateľné.

Nesprávne dátum:

Dôvod tejto chyby je iba ten, že v roku 2019 neexistuje dátum „29. február“, pretože nejde o priestupný rok.

Deklarovať @date_time_value varchar (100) = '2019-02-29 21:02:04' vyberte obsadenie (@date_time_value ako datetime2) ako date_time_value

Obrázok 5: Chyba vyvolaná rokom 2019 nie je priestupným rokom, preto k dátumu nemá 29. február

Správny:

Deklarovať @date_time_value varchar (100) = '2019-02-28 21:02:04' vyberte obsadenie (@date_time_value ako datetime2) ako date_time_value

Obrázok 6: Správny dátum

Formát dátumu ISO 8601:

Aj keď je k dispozícii množstvo formátov na manipuláciu s hodnotami dátumov, pri práci pre globálnu / medzinárodnú masu môže byť problémom použiteľnosti zvoliť reprezentáciu datetime. Mali by ste sa teda vyhnúť literálom zameraným na konkrétny dátum a čas. Ak vezmeme do úvahy tento dátum „3. 8. 2018“, bude sa v rôznych regiónoch sveta interpretovať rôznymi spôsobmi.

• V anglickom štýle sa interpretuje ako „8. marca 2018“
• V európskom štýle sa interpretuje ako „3. augusta 2018“

Našťastie existuje jedna alternatíva v medzinárodnom formáte dátumu vyvinutom ISO. Globálny štandardný formát ISO 8601 „RRRR-MM-DDThh: mm: ss“ je jazykovo nezávislejšou voľbou pre reťazcové literály a rieši všetky tieto problémy. Zatiaľ čo „rrrr“ je rok, „mm“ je mesiac a „dd“ je deň. Takže dátum „8. marca 2018“ v medzinárodnom formáte ISO je napísaný ako „2018-03-08“. Preto je formát ISO najlepšou voľbou pre zobrazenie dátumu.

Deklarovať @date_time_value varchar (100) = '2019-03-28 21:02:04' vyberte konvertovať (datetime2, @ date_time_value, 126) ako [rrrr-mm-ddThh: mi: ss.mmm]

Obrázok 7: Medzinárodný štandardný formát dátumu ISO 8601

Odporúčania:

Dúfajme, že tento článok pomôže zmierniť zmätok, ktorý som v komunite často videl o hodnotách dátumu a času. Odporúča sa však nikdy neuchovávať dátumy v textovom type (varchar, char, nvarchar, nchar alebo text). Hodnotu dátumu vždy ukladať v stĺpcoch typu DATE, DATETIME a najlepšie DATETIME2 (poskytuje presnejšie údaje) a ponechať formátovanie informácií o dátume do vrstvy používateľského rozhrania namiesto toho, aby boli načítané z databázy.