SQLShack
Wprowadzenie
Częstym zadaniem dla początkujących jest nauczenie się jak wykonać SQL Convert Date i pracować nad konwersją daty do innych typów danych lub przekształceniem innych typów danych do daty.
Tutaj w tym artykule wyjaśnimy, jak pracować i konwertować daty do różnych formatów lub odwrotnie.
Wymagania
- Zainstalowany SQL Server. Starting in SQL Server 2008
Przykład
Pierwszy przykład będzie prosty, mamy kolumnę varchar z datą w tabeli, ale potrzebujemy przekonwertować varchar na datę. Musimy wykonać SQL convert date.
Tutaj jest skrypt do tworzenia tabeli z danymi:
|
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′)
|
Chcemy przekonwertować kolumnę data z nvarchar(100) na datę.
Aby to zrobić, spróbujemy zmodyfikować wygląd tabeli:
Spróbujemy zmienić Typ danych na smalldatetime:
Otrzymamy następujący komunikat o błędzie:
Zapisywanie zmian jest niedozwolone. Zmiany, których dokonałeś wymagają usunięcia i ponownego utworzenia następujących tabel. Albo wprowadziłeś zmiany w tabeli, której nie można ponownie utworzyć, albo włączyłeś opcję Zapobiegaj zapisywaniu zmian, które wymagają ponownego utworzenia tabeli.
Aby rozwiązać ten błąd, w SSMS przejdź do menu Narzędzia > Opcje:
W Opcjach przejdź do zakładki Projektanci >Projektanci tabel i baz danych i usuń zaznaczenie opcji Zapobiegaj zapisywaniu zmian, które wymagają ponownego utworzenia tabeli:
Ta opcja wyłączy możliwość uniemożliwienia zapisywania odtworzenia tabeli. Gdy zmodyfikujesz typ danych kolumny, wymaga to ponownego utworzenia tabeli.
Teraz możesz zapisać projekt, a Twoja tabela zostanie przekonwertowana na datę, a konwersja daty w SQL jest zakończona:
Funkcje konwersji
T-SQL zawiera funkcje do konwersji typów danych. Użyjemy CAST i CONVERT, aby wykonać SQL-ową konwersję daty.
Zacznijmy najpierw od CAST:
Jak przekonwertować z varchar, nvarchar, char, nchar na sql date używając CAST
Poniższy przykład, pokaże jak przekonwertować znaki na typ daty datetime używając funkcji CAST:
|
1
2
|
declare @vardate varchar(100)=’03-04-2016′
select CAST(@vardate AS datetime) AS dataconverted;
|
W przykładzie zadeklarowano zmienną o nazwie vardate, a następnie zmienna ta będąca varcharem została przekonwertowana na datetime za pomocą funkcji CAST.
Uwaga: Aby uzyskać więcej informacji na temat funkcji CAST, zapoznaj się z tym linkiem: CAST i CONVERT (Transact-SQL)
Jak zrobić SQL convert date from varchar, nvarchar, char, nchar to date using CONVERT
CONVERT jest funkcją, która może zrobić to samo, co CAST w poprzednim scenariuszu.
|
1
2
|
declare @vardate varchar(100)=’03-04-2016′
select CONVERT(datetime, @vardate) as dataconverted
|
Kod T-SQL robi to samo co cast, ale korzysta z funkcji CONVERT. Zaletą CONVERT jest to, że można łatwo zmienić format daty za pomocą argumentu style.
Na przykład, jeśli chcesz uzyskać datę w formacie ISO, możesz użyć następującego zdania T-SQL:
|
1
|
select CONVERT(nvarchar(30),getdate(), 121) as isoformat
|
Jak przekonwertować datę sql na różne formaty w T-SQL
Poniższy przykład pokazuje, jak przekonwertować format daty na różne formaty.
Dla formatu Japananes:
|
1
|
select CONVERT(nvarchar(30),getdate(), 111) as Japanformat
|
Dla formatu USA:
|
1
|
select CONVERT(nvarchar(30),getdate(), 110) as USAformat
|
Dla formatu ANSI:
|
1
|
select CONVERT(nvarchar(30),getdate(), 102) as ANSIformat
|
Dla formatu brytyjskiego:
|
1
|
select CONVERT(nvarchar(30),getdate(), 103) as Britishformat
|
Dla formatu niemieckiego:
|
1
|
select CONVERT(nvarchar(30),getdate(), 104) as Germanformat
|
Dla formatu włoskiego:
|
1
|
select CONVERT(nvarchar(30),getdate(), 105) as Italianformat
|
Dla domyślnego formatu europejskiego:
|
1
|
select CONVERT(nvarchar(30),getdate(), 113) as EuropeDefaultformat
|
Dla ODBC Canonical:
|
1
|
select CONVERT(nvarchar(30),getdate(), 120) as ODBCCanonicalformat
|
Zawsze masz możliwość użycia funkcji FORMAT, aby uzyskać datę w formacie, który chcesz:
|
1
|
SELECT FORMAT( getdate(), 'dd/MM/rr’)
|
FORMAT jest łatwiejszy w obsłudze dat i używaniu formatu preferowanego przez użytkownika, ponieważ nie trzeba znać stylu. Jednak z mojego doświadczenia widzę wiele kodu używającego funkcji CAST i CONVERT, więc lepiej je znać.
Uwaga: Więcej informacji na temat funkcji FORMAT można znaleźć pod tym linkiem: FORMAT (Transact-SQL)
Problemy związane z operacjami SQL convert date
Gdy próbujesz przekonwertować na datę, nie zawsze jest to możliwe. Poniższy przykład pokazuje często występujący błąd:
|
1
2
|
declare @vardate varchar(100)=’11242016′
select CONVERT(datetime, @vardate) as dataconverted
|
Komunikat o błędzie jest następujący:
Msg 242, Level 16, State 3, Line 22
Konwersja typu danych varchar do typu danych datetime spowodowała otrzymanie wartości spoza zakresu.
Potrzebne są separatory dla daty, takie jak „/”, „.” lub „-„.
Poniższy przykład, modyfikuje ciąg znaków z 11242016 na 11-24-2016, a następnie konwertuje do daty 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
|
Używamy substring do konkatenacji „-„, aby użyć akceptowalnego formatu daty, a następnie używamy funkcji CONVERT do konwersji znaków na datę sql.
Typy danych daty
W SQL Server istnieje kilka typów datatypów:
- Time zwraca godziny, minuty, sekundy i nanosekundy (hh:mm:ss.nnnnnn)
- Date zwraca rok, miesiące i dni (yyyy-mm-dd)
- Datetime zwraca dane o takim formacie: YYYY-MM-DD hh:mm:ss
- Smalldatetime zwraca datę z tym formatem: YYYY-MM-DD hh:mm:ss
- Datetime2 jest podobny do Datetime, ale ma większą precyzję (YYYYY-MM-DD hh:mm:ss)
- Datetimeoffset ma on precyzję datetime2, ale jest używany dla stref czasowych w UTC
SQL convert date to integer
Jeśli użyjesz CONVERT lub CAST do konwersji datetime na integer, zwróci on liczbę dni od 1900 do podanej daty.
Na przykład następujący kod T-SQL wyświetli liczbę dni od roku 1900 do dnia dzisiejszego:
|
1
|
SELECT CONVERT(INT, GETDATE())
|
Można również konwertować na liczbę całkowitą rok, miesiące, dni itd. wartości datetime. Poniższy kod pokazuje, jak przechowywać w zmiennych całkowitych dzień, miesiąc i rok wartości datetime:
|
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
Uwaga: Poniższy link zawiera FAQ na temat funkcji i dat w SQL Server: FAQ about Dates in SQL Server
Podsumowanie
W tym artykule dowiedzieliśmy się jak wykonać SQL convert date w SQL Server. Dowiedzieliśmy się jak modyfikować typ danych w tabeli, jak używać funkcji CAST, CONVERT i FORMAT. Poznaliśmy również różne rodzaje typów danych SQL.
- Autor
- Recent Posts
View all posts by Daniel Calbimonte
- Pytania na rozmowę kwalifikacyjną SQL – 3 września, 2020
- Certyfikacje SQL – 7 sierpnia 2020
- Historia TSQL – 3 września 2019
.