Jak opravit chybu „Převod se nezdařil při převodu data a / nebo času z řetězce znaků“?

Existuje mnoho případů, kdy se data a časy nezobrazí ve formátu, jaký chcete, ani výstup dotazu neodpovídá potřebám diváků. Existuje několik vestavěných funkcí serveru SQL Server pro formátování řetězce data podle vašich potřeb, ale aby byl řetězec interpretován serverem SQL Server a aby se zabránilo chybám při převodu, měl by být ve správném formátu. Když se pokusíme převést datum nebo čas z řetězce znaků, někdy dojde k chybě. 'Převod se nezdařil při převodu data a / nebo času z řetězce znaků.'

Obrázek 1: Chyba převodu data a / nebo času z řetězce znaků

Výše uvedená chyba obvykle nastává, když datový literál není správný a nelze jej převést z řetězce na DateTime nebo date. Výsledkem této chyby je řada důvodů, které si podrobně rozebereme spolu se sadou řešení.

Příklad 1:

Velká Británie Zápis data a času zobrazuje datum ve formátu den-měsíc-rok (10. ledna 2015 nebo 1. 1. 2015), čehož můžeme dosáhnout pomocí funkce SQL Server built_in „převést“ pomocí stylu formátování 103.

Tady v příkladu níže vidíme, že zadaný řetězec data je ve špatném formátu. Nejprve poskytuje měsíc, poté dny a poslední rok, což je špatné a nemůže být interpretováno serverem SQL, což má za následek chybu. Správný formát pro převod data stylu ve Velké Británii pomocí stylu data „103“ je „dd / mm / rrrr“.

Špatný formát:

Deklarovat @date_time_value varchar (100) = '16.10.2015 21:02:04' vyberte CONVERT (datetime2, @date_time_value, 103) jako UK_Date_Time_Style

Obrázek 2: Špatný formát data, který má za následek chybu

Správný formát:

Britský a francouzský formát data je 103 = „dd / mm / rrrr“ nebo 3 = „dd / mm / rrr“. Zde 103 a 3 jsou styly dat.

Deklarovat @date_time_value varchar (100) = '10 / 1/15 21:02:04 'vyberte CONVERT (datetime2, @date_time_value, 103) jako Date_Time_Style

Obrázek 3: Správný formát data v britském / francouzském formátu data „dd / mm / rrrr“

Deklarovat @date_time_value varchar (100) = '10 / 1/15 21:02:04 'vyberte CONVERT (datetime2, @date_time_value, 3) jako UK_Date_Time_Style

Obrázek 4: Správný formát data ve stylu britského / francouzského data „dd / mm / rr“

Příklad 2:

Převod řetězce na datum na serveru SQL někdy vede k chybě, nikoli kvůli použitým formátům data nebo času, ale spíše proto, že se pokoušíte uložit nesprávné informace, které nejsou pro schéma přijatelné.

Špatné datum:

Důvodem následující chyby je pouze to, že v roce 2019 neexistuje datum jako „29. února“, protože se nejedná o přestupný rok.

Deklarovat @date_time_value varchar (100) = '2019-02-29 21:02:04' vyberte cast (@date_time_value jako datetime2) jako date_time_value

Obrázek 5: Chyba vyvolaná jako rok 2019 není přestupným rokem, takže jako datum nemá 29. února

Správný:

Deklarovat @date_time_value varchar (100) = '2019-02-28 21:02:04' vyberte cast (@date_time_value jako datetime2) jako date_time_value

Obrázek 6: Správné datum

Formát data ISO 8601:

Ačkoli pro manipulaci s hodnotami dat je k dispozici řada formátů, při práci s globální / mezinárodní hromadou může být problém s použitelností reprezentace datetime. Je tedy třeba se vyhnout literálům data a času specifickým pro danou kulturu. Pokud vezmeme v úvahu toto datum „3. 8. 2018“, bude v různých regionech světa interpretováno různými způsoby.

• Ve stylu Velké Británie je interpretován jako „8. března 2018“
• V evropském stylu je interpretován jako „3. srpna 2018“

Naštěstí existuje jedna alternativa v mezinárodním formátu data vyvinutém ISO. Globální standardní formát ISO 8601 „RRRR-MM-DDThh: mm: ss“ je jazykově nezávislejší volbou pro řetězcové literály a řeší všechny tyto problémy. Zatímco „rrrr“ je rok, „mm“ je měsíc a „dd“ je den. Datum „8. března 2018“ v mezinárodním formátu ISO je tedy napsáno jako „2018-03-08“. Formát ISO je tedy nejlepší volbou pro zobrazení data.

Deklarovat @date_time_value varchar (100) = '2019-03-28 21:02:04' vyberte převést (datetime2, @ date_time_value, 126) jako [rrrr-mm-ddThh: mi: ss.mmm]

Obrázek 7: Mezinárodní standardní formát data ISO 8601

Doporučení:

Doufejme, že tento článek pomůže zmírnit zmatek, který jsem v komunitě často viděl ohledně hodnot data a času. Doporučuje se však nikdy neukládat data do textového typu (varchar, char, nvarchar, nchar nebo text) Vždy ukládat hodnotu data do sloupců typu DATE, DATETIME a nejlépe DATETIME2 (poskytuje přesnější) a ponechat formátování informací o datu namísto načtení z databáze do vrstvy uživatelského rozhraní.