SQLShack
Bevezetés
Az újoncok közös feladata, hogy megtanulják, hogyan kell csinálni egy SQL convert date és a munka, hogy konvertálja őket dátumot más adattípusokba, vagy más adattípusok Date-re történő átalakítása.
Itt ebben a cikkben elmagyarázzuk, hogyan kell dolgozni, és konvertálni dátumokat különböző formátumokra vagy fordítva.
Követelmények
- SQL Server telepítve. SQL Server 2008-tól kezdve
Példa
Az első példa egyszerű lesz, van egy varchar oszlopunk egy dátummal egy táblázatban, de a varchart dátumra kell konvertálnunk. Szükségünk van egy SQL convert date-re.
Itt van a szkript, amely létrehozza a táblát az adatokkal:
|
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′)
|
A dátum oszlopot nvarchar(100)-ról dátumra szeretnénk konvertálni.
Ehhez megpróbáljuk módosítani a táblázat felépítését:
Megpróbáljuk az Adattípust smalldatetime-ra módosítani:
A következő hibaüzenetet kapjuk:
A módosítások mentése nem engedélyezett. Az elvégzett módosítások miatt a következő táblákat el kell dobni és újra létre kell hozni. Ön vagy olyan táblán végzett módosításokat, amelyet nem lehet újra létrehozni, vagy engedélyezte a Táblák újbóli létrehozását igénylő módosítások mentésének megakadályozása opciót.
A hiba elhárításához az SSMS-ben lépjen az Eszközök > Beállítások menüpontba:
A Beállításokban lépjen a Tervezők >Tábla- és adatbázis-tervezők menüpontba, és törölje a Táblák újbóli létrehozását igénylő módosítások mentésének megakadályozása opciót:
Ez a beállítás letiltja a táblázat újrateremtésének mentését megakadályozó lehetőséget. Ha módosítja az oszlop adattípusát, az táblázat újrateremtést igényel.
Most már elmentheti a tervezést, és a táblázata dátumra lesz konvertálva, és az SQL konvertálási dátum elkészül:
Konvertáló függvények
A T-SQL tartalmaz függvényeket az adattípusok konvertálására. A CAST és a CONVERT függvényeket fogjuk használni az SQL dátumkonvertáláshoz.
Kezdjük először a CAST-tal:
How to convert from varchar, nvarchar, char, nchar to sql date using CAST
A következő példa, megmutatja, hogyan konvertáljuk a karaktereket datetime dátumtípussá a CAST függvény segítségével:
|
1
2
|
declare @vardate varchar(100)=’03-04-2016′
select CAST(@vardate AS datetime) AS dataconverted;
|
A példa deklarál egy vardate nevű változót, majd ezt a varchar változót a CAST függvény segítségével datetime változóvá konvertálja.
Megjegyzés: A CAST függvénnyel kapcsolatos további információkat ezen a linken talál: CAST és CONVERT (Transact-SQL)
How to do a SQL convert date from varchar, nvarchar, char, nchar to date using CONVERT
CONVERT is a function that can do the same as CAST in the previous scenario.
|
1
2
|
declare @vardate varchar(100)=’03-04-2016′
select CONVERT(datetime, @vardate) as dataconverted
|
A T-SQL kód ugyanazt csinálja, mint a cast, de a CONVERT függvényt használja. A CONVERT előnye, hogy a stílus argumentummal könnyen megváltoztathatjuk a dátum formátumát.
Ha például a dátumot ISO formátumban szeretnénk, akkor a következő T-SQL mondatot használhatjuk:
|
1
|
select CONVERT(nvarchar(30),getdate(), 121) as isoformat
|
Hogyan konvertáljuk az sql dátumot különböző formátumokra T-SQL-ben
A következő példa azt mutatja, hogyan konvertálhatjuk a dátumformátumot különböző formátumokra.
Japán formátumhoz:
|
1
|
select CONVERT(nvarchar(30),getdate(), 111) as Japanformat
|
USA formátumhoz:
|
1
|
select CONVERT(nvarchar(30),getdate(), 110) as USAformat
|
ANSI formátum esetén:
|
1
|
select CONVERT(nvarchar(30),getdate(), 102) as ANSIformat
|
Brit formátum esetén:
|
1
|
select CONVERT(nvarchar(30),getdate(), 103) as Britishformat
|
Német formátumhoz:
|
1
|
select CONVERT(nvarchar(30),getdate(), 104) as Germanformat
|
Olasz formátumhoz:
|
1
|
select CONVERT(nvarchar(30),getdate(), 105) as Italianformat
|
Az európai alapértelmezett formátumhoz:
|
1
|
select CONVERT(nvarchar(30),getdate(), 113) as EuropeDefaultformat
|
ODBC Canonical esetén:
|
1
|
select CONVERT(nvarchar(30),getdate(), 120) as ODBCCanonicalformat
|
Mindig lehetősége van a FORMAT függvényt használni, hogy a dátumot a kívánt formátumban kapja meg:
|
1
|
SELECT FORMAT( getdate(), ‘dd/MM/yy’)
|
A FORMAT segítségével könnyebb kezelni a dátumokat és az általad preferált formátumot használni, mert nem kell ismerned a stílust. Tapasztalatom szerint azonban sok olyan kódot látok, amely a CAST és a CONVERT függvényeket használja, ezért jobb, ha ismeri őket.
Megjegyzés: A FORMAT függvényről további információkat ezen a linken talál: FORMAT (Transact-SQL)
Problems related to SQL convert date operations
Amikor megpróbál dátumra konvertálni, ez nem mindig lehetséges. Az alábbi példa egy gyakori hibát mutat be:
|
1
2
|
declare @vardate varchar(100)=’11242016′
select CONVERT(datetime, @vardate) as dataconverted
|
A hibaüzenet a következő:
Msg 242, Level 16, State 3, Line 22
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
A dátumhoz elválasztójelekre van szükség, mint például egy “/”, egy “.” vagy egy “-“.
A következő példa a karakterláncot 11242016-ról 11-24-2016-ra módosítja, majd sql dátumra konvertálja:
|
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
|
A substring segítségével a “-“-et összekapcsoljuk, hogy elfogadható dátumformátumot használjunk, majd a CONVERT függvénnyel a karaktereket sql dátumokká alakítjuk.
Dátum adattípusok
Az SQL Serverben többféle dátum adattípus létezik:
- Time az órákat, perceket, másodperceket és nanoszekundumokat adja vissza (hh:mm:ss.nnnnnn)
- Date az évet, hónapokat és napokat adja vissza (yyyy-mm-dd)
- Datetime ilyen formátumú adatokat ad vissza: hh:mm:ss
- Smalldatetime ezzel a formátummal adja vissza a dátumot: mm:ss
- Datetime2 hasonló a Datetime-hoz, de nagyobb pontossággal (YYYY-MM-DD hh:mm:ss)
- Datetimeoffset a datetime2 pontosságával rendelkezik, de UTC időzónákhoz használják
SQL dátum egész számra konvertálása
Ha a CONVERT vagy CAST segítségével egy datetime-ot egész számra konvertál, akkor az 1900 óta eltelt napok számát adja vissza a megadott dátumig.
A következő T-SQL kód például az 1900-tól a mai napig eltelt napok számát mutatja:
|
1
|
SELECT CONVERT(INT, GETDATE())
|
Egy datetime érték évét, hónapját, napját stb. is egész számra konvertálhatja. A következő kód azt mutatja, hogyan tárolhatjuk egész szám változókban egy dátumidő érték napját, hónapját és évét:
|
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
|
Common Questions about SQL convert date in SQL Server
Megjegyzés: Az alábbi linken az SQL Server függvényekkel és dátumokkal kapcsolatos GYIK található: GYIK a dátumokkal kapcsolatban az SQL Serverben
Következtetések
Ebben a cikkben megtanultuk, hogyan kell SQL dátumkonvertálást végezni az SQL Serverben. Megtanultuk, hogyan módosíthatjuk az adattípust egy táblázatban, hogyan használhatjuk a CAST, CONVERT és FORMAT függvényeket. Megismertük az SQL adattípusok különböző típusait is.
- Author
- Recent Posts
Világszerte dolgozott a kormánynak, olajvállalatoknak, weboldalaknak, magazinoknak és egyetemeknek. Daniel rendszeresen tart előadásokat SQL Servers konferenciákon és blogokon is. SQL Server képzési anyagokat ír a tanúsítási vizsgákhoz.
Az SQLShack-cikkek spanyolra fordításában is segít
Daniel Calbimonte összes bejegyzése
- SQL interjúkérdések – szeptember 3, 2020
- SQL tanúsítványok – 2020. augusztus 7.
- TSQL történelem – 2019. szeptember 3.