SELECT Explanation, Example FROM Pro.Knowledge
FacebookRSS

Funkcje konwersji typów, CAST i CONVERT

Jednymi z częściej stosowanych skalarnych funkcji wbudowanych są CAST oraz CONVERT. Służą one do konwersji typów danych na inne (np. liczby na ciąg znaków). Szczególnie pomocne, gdy chcesz np. dokonać konkatenacji (złączenia) atrybutów w ciągi znakowe (stringi) z wartościami typu data czy liczbami.

W T-SQL trzeba zadbać o sensowność operacji pod względem typów danych na któych operujemy. Trudno porównać wartość tekstową ‚Seattle’ z liczbą 2. Poniższe dwa zapytania zawierają typowe błędy, związane ze znaczącą różnicą porównywnaych typów czy przekształcacnych danych (składanie stringu).

use northwind
go
 
-- błąd konwersji w warunku WHERE 
-- porównanie wartości tekstowej z liczbą
select * from dbo.Employees
where City = 2
 
-- błąd konwersji w SELECT
-- próba połączenia stringów z typem danych date/time
select LastName + ' urodzony : ' + BirthDate
from dbo.Employees
Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the nvarchar value 'Seattle' to data type int.

Msg 241, Level 16, State 1, Line 3
Conversion failed when converting date and/or time from character string.

W niektórych przypadkach, można działać na różniących się typach z tej samej rodziny. Wtedy gdy możliwa jest niejawna konwersja – np. wartość liczbowa typu całkowitego oraz zmiennoprzecinkowa. Jeśli jednak chcemy być pewni wyniku, powinniśmy zawsze zatroszczyć się o pełną zgodność typów. Funkcje CAST oraz CONVERT służą właśnie do osiągnięcia tego celu.


CAST(wartość_konwertowana AS typ_danych) jest podstawową funkcją konwersji zgodną ze standardem ANSI. Jej działanie ogranicza się do bezpośredniej konwersji danej wartości na inny typ danych podany jako drugi parametr funkcji (po słowie kluczowym AS). Z uwagi na jej kompatybilność z ANSI, powinna być stosowana zawsze, gdy nie potrzeba określać styli (np. w konwersji dat).

Jej możliwości są jednak nieco ograniczone w stosunku do CONVERT, ale w wielu sytuacjach nie ma to znaczenia. Możemy stosować je wymiennie.

Konwersję używamy np. do łączenia danych różnego typu, chcąc uzyskać w rezultacie ciąg znakowy.

USE Northwind
GO
 
SELECT LastName + ' hired : ' +  CAST (HireDate as varchar(100)) as EmpInfo,
       HireDate
FROM dbo.Employees;

FN_CAST_01
Zauważ, że w tym przypadku wartość daty została przekonwertowana na określony styl. Funkcja CAST nie posiada możliwości wyboru stylu, dlatego jeśli chcemy otrzymać samą datę (w formacie YYYY-MM-DD), powinniśmy w tej sytuacji skorzystać z funkcji CONVERT ze wskazanym stylem.


CONVERT (typ_danych, wartość_konwertowana, opcjonalnie_styl) potrafi wszystko to co CAST, dodatkowo możemy określić styl typu danych. Ma on znaczenie zwłaszcza w przypadku konwersji na typy danych związanych z datą i czasem, a także liczbowe (określenie precyzji), XML i binarne. Szczegółowy opis styli znajdziesz tutaj (MSDN).

USE Northwind
GO
 
SELECT 'Hired : ' +  CAST (HireDate as varchar(10)) as konwersjaCast,
        CONVERT(varchar(10),HireDate ,120) as KonwersjaConvertStyles, 
        CAST(CAST (HireDate as date) as varchar(10)) as DblCast,
        HireDate
FROM dbo.Employees

FN_CAST_02
Podobne operacje musimy wykonać jeśli chcemy łączyć ze sobą wartości liczbowe i tekstowe. Poniższy przykład obnaża po raz kolejny ograniczenia CAST (tym razem związane z zaokrąglaniem wartości).

USE AdventureWorks2008
GO
 
SELECT salesOrderId, 'Cast  = ' + CAST(TotalDue as varchar(100)) + 
       '; Convert = ' + CONVERT(varchar(100),TotalDue,2) as Summary, 
       TotalDue
FROM Sales.SalesOrderHeader
WHERE TotalDue BETWEEN 123 AND 124

FN_CAST_CONVERT_03
Trzeba pamiętać, że w ten sposób wykonywana konwersja, musi być możliwa dla wszystkich wartości w danej kolumnie. W przeciwnym razie, jeśli chodzi dla jednej wartości nie będzie możliwa zmiany typu danych – zapytanie zakończy się niepowodzeniem. Zdarza się tak zazwyczaj w „zabrudzonych” bazach, w których dane są niespójne na wskutek np. importów z pominięciem reguł itp. akcji.


W SQL Server 2012 wprowadzono nowe funkcje związane z konwersją typów danych. Szczególnie przydatne to – TRY_CAST oraz TRY_CONVERT. Ich pojawienie upraszcza „czyszczenie danych”.
Funkcje te działają identycznie do CAST oraz CONVERT z tym że dla danych, dla których konwersja nie jest możliwa, zamiast błędu, zwracana jest wartość NULL.

2 thoughts on “Funkcje konwersji typów, CAST i CONVERT

  1. USE AdventureWorks2008
    GO
     
    SELECT salesOrderId, 'Cast  = ' + CAST(TotalDue as varchar(100)) + 
           '; Convert = ' + CONVERT(varchar(100),TotalDue,2) as Summary, 
           TotalDue

    W zasadzie tego nie zweryfikowałem, aczkolwiek wydaje Mi się, że jest niezgodność miedzy składnią polecenia SQL, a tabelą wynikową. Jeśli dobrze wszystko zrozumiałem funkcja CAST nie ma możliwości zwrócenia wyniku z użyciem stylu (Tu zaokrąglenia), a po tabelce wynikowej można wnioskować, że tak jest.

    Wydaje Mi się, że w miejscu gdzie powinien być wynik działania funkcji konwersji CAST jest wynik funkcji CONVERT.

    • No właśnie dlatego zawsze lepiej sprawdzać :) CAST konwertuje bez możliwości określenia stylu, bierze domyślne lub zdefiniowane dla sesji style…. w tym przypadku tak właśnie będzie. Pozdrawiam !

Leave a comment

Twój adres email nie zostanie opublikowany.

Uzupełnij równanie (SPAM protection) *