SQLShack

admin 0 Comments Articles

SQLShack

Úvod

Častým úkolem pro nováčky je naučit se SQL konvertovat datum a pracovat s nimi tak, aby převedli datum na jiné datové typy nebo převedli jiné datové typy na datum.

Zde v tomto článku si vysvětlíme, jak pracovat a převádět data na jiné formáty nebo naopak.

Požadavky

  1. SQL Server nainstalován. Počínaje SQL Serverem 2008

Příklad

První příklad bude jednoduchý, v tabulce máme sloupec varchar s datem, ale potřebujeme převést varchar na datum. Musíme provést SQL konverzi data.

Zde je skript pro vytvoření tabulky s daty:

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE .(
NOT NULL,
(100) NULL,
CONSTRAINT PRIMARY KEY CLUSTERED
(
ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
) ON
GO
INSERT . (, ) VALUES (1, N’02-03-2005′)
INSERT . (, ) VALUES (2, N’03-05-2006′)
INSERT . (, ) VALUES (3, n’04-05-2011′)

Chceme převést sloupec date z nvarchar(100) na datum.

K tomu se pokusíme upravit návrh tabulky:

Pokusíme se změnit Typ dat na smalldatetime:

Zobrazí se následující chybové hlášení:

Ukládání změn není povoleno. Provedené změny vyžadují zrušení a nové vytvoření následujících tabulek. Buď jste provedli změny v tabulce, kterou nelze znovu vytvořit, nebo jste zapnuli volbu Zabránit ukládání změn, které vyžadují nové vytvoření tabulky.

Chcete-li tuto chybu vyřešit, přejděte v SSMS do nabídky Nástroje > Možnosti:

V Možnostech přejděte do nabídky Návrháři > Návrháři tabulek a databází a zrušte zaškrtnutí volby Zabránit ukládání změn, které vyžadují nové vytvoření tabulky:

Tato volba zakáže možnost zabránit ukládání obnovení tabulky. Když změníte datový typ sloupce, vyžaduje to opětovné vytvoření tabulky:

Nyní můžete uložit návrh a vaše tabulka bude převedena na data a konverze dat SQL bude dokončena:

Konverzní funkce

T-SQL obsahuje funkce pro konverzi datových typů. K provedení SQL konverze dat použijeme CAST a CONVERT.

Začneme nejprve s CAST:

Jak převést z varchar, nvarchar, char, nchar na sql date pomocí CAST

Následující příklad, ukáže, jak převést znaky na datum typu date pomocí funkce CAST:

1
2
declare @vardate varchar(100)=’03-04-2016′
select CAST(@vardate AS datetime) AS dataconverted;

Příklad deklaruje proměnnou s názvem vardate a poté je tato proměnná, která je varchar, převedena na datetime pomocí funkce CAST.

Poznámka: Další informace o funkci CAST naleznete na tomto odkazu: CAST a CONVERT (Transact-SQL)

Jak provést SQL převod data z varchar, nvarchar, char, nchar na datum pomocí funkce CONVERT

CONVERT je funkce, která umí totéž co CAST v předchozím scénáři.

1
2
declare @vardate varchar(100)=’03-04-2016′
select CONVERT(datetime, @vardate) as dataconverted

Kód T-SQL provádí totéž co cast, ale používá funkci CONVERT. Výhodou funkce CONVERT je, že můžete snadno změnit formát data pomocí argumentu style.

Pokud například chcete datum ve formátu ISO, můžete použít následující větu jazyka T-SQL:

1
select CONVERT(nvarchar(30),getdate(), 121) as isoformat

Jak převést sql datum do různých formátů v T-SQL

Následující příklad ukazuje, jak převést formát data do různých formátů.

Pro formát Japananes:

1
select CONVERT(nvarchar(30),getdate(), 111) as Japanformat

Pro formát USA:

1
select CONVERT(nvarchar(30),getdate(), 110) as USAformat

Pro formát ANSI:

1
select CONVERT(nvarchar(30),getdate(), 102) as ANSIformat

Pro britský formát:

1
select CONVERT(nvarchar(30),getdate(), 103) as Britishformat

Pro německý formát:

1
select CONVERT(nvarchar(30),getdate(), 104) as Germanformat

Pro italský formát:

1
select CONVERT(nvarchar(30),getdate(), 105) as Italianformat

Pro evropský výchozí formát:

1
select CONVERT(nvarchar(30),getdate(), 113) as EuropeDefaultformat

Pro ODBC Canonical:

1
select CONVERT(nvarchar(30),getdate(), 120) as ODBCCanonicalformat

Vždy máte možnost použít funkci FORMAT a získat datum v požadovaném formátu:

1
SELECT FORMAT( getdate(), ‚dd/MM/yy‘)

FORMAT je jednodušší pro práci s daty a použití formátu podle vašich preferencí, protože nemusíte znát styl. Podle mých zkušeností se však setkávám se spoustou kódu, který používá funkce CAST a CONVERT, takže je lepší je znát.

Poznámka: Další informace o funkci FORMAT najdete na tomto odkazu:

Problémy týkající se operací SQL s převodem data

Při pokusu o převod na datum to není vždy možné. Následující příklad ukazuje běžnou chybu:

1
2
declare @vardate varchar(100)=’11242016′
select CONVERT(datetime, @vardate) as dataconverted

Chybové hlášení je následující:

Msg 242, Level 16, State 3, Line 22

Převod datového typu varchar na datový typ datetime vedl k hodnotě mimo rozsah.

Pro datum potřebujete oddělovače jako „/“, „.“ nebo „-„.

Následující příklad upraví řetězec z 11242016 na 11-24-2016 a poté jej převede na datum sql:

1
2
3
4
5
declare @vardate varchar(100)=’11242016′
set @vardate= SUBSTRING(@vardate, 1, 2)+‘-‚+ SUBSTRING(@vardate, 3, 2)+‘-‚+SUBSTRING(@vardate, 5, 4)
select CONVERT(date, @vardate) as dataconverted

Pomocí substringu spojíme „-„, abychom použili přijatelný formát data, a pak pomocí funkce CONVERT převedeme znaky na sql datum.

Datové typy data

V SQL Serveru existuje několik typů datových typů data:

  • Time vrací hodiny, minuty, sekundy a nanosekundy (hh:mm:ss.nnnnnn)
  • Date vrací rok, měsíce a dny (rrrr-mm-dd)
  • Datetime vrací data v tomto formátu: RRRR-MM-DD hh:mm:ss
  • Smalldatetime vrací datum v tomto formátu: RRRR-MM-DD hh:mm:ss
  • Datetime2 je podobný Datetime, ale má větší přesnost (RRRR-MM-DD hh:mm:ss)
  • Datetimeoffset má přesnost datetime2, ale používá se pro časová pásma v UTC

SQL převést datum na celé číslo

Pokud použijete CONVERT nebo CAST pro převod datetime na celé číslo, vrátí se počet dní od roku 1900 do zadaného data.

Například následující kód T-SQL zobrazí počet dní od roku 1900 do dneška:

1
SELECT CONVERT(INT, GETDATE()).

Na celé číslo můžete převést také rok, měsíce, dny atd. z hodnoty datetime. Následující kód ukazuje, jak uložit do celočíselných proměnných den, měsíc a rok hodnoty data:

1
2
3
4
5
declare @year int = year(getdate())
declare @month int = month(getdate())
declare @day int = day(getdate())
select @year as year,@month as month,@day as day

Obvyklé otázky týkající se převodu data v SQL Serveru

Poznámka: Následující odkaz obsahuje nejčastější dotazy týkající se funkcí a dat v SQL Serveru: V tomto článku jsme se naučili, jak v SQL Serveru provést SQL konverzi data

Závěry

. Dozvěděli jsme se, jak upravit datový typ v tabulce, jak používat funkce CAST, CONVERT a FORMAT. Seznámili jsme se také s různými typy datových typů SQL.

  • Autor
  • Poslední příspěvky
Daniel Calbimonte je držitelem titulu Microsoft Most Valuable Professional, Microsoft Certified Trainer a Microsoft Certified IT Professional pro SQL Server. Je uznávaným autorem SSIS, učitelem na IT akademiích a má více než 13 let zkušeností s prací s různými databázemi.
Pracoval pro vládu, ropné společnosti, webové stránky, časopisy a univerzity po celém světě. Daniel také pravidelně přednáší na konferencích o SQL Serverech a píše blogy. Je autorem školicích materiálů k certifikačním zkouškám SQL Server.
Pomáhá také s překlady článků na serveru SQLShack do španělštiny
Zobrazit všechny příspěvky od Daniel Calbimonte

Nejnovější příspěvky od Daniel Calbimonte (zobrazit všechny)
  • Otázky k pohovorům SQL – 3. září, 2020
  • Certifikace SQL – 7. srpna 2020
  • Historie TSQL – 3. září 2019

Napsat komentář

Vaše e-mailová adresa nebude zveřejněna.