Błedy konwersji typów SQL, zaokrąglenia, obięcia, przekroczenia zakresu typu

Programując w SQL zastanawiasz się może czasami jaki typ wybrać dla zmiennych lub kolumn w tabelach. SQL Server sam podejmuje czasami decyzję o potrzebie wykonania pewnych konwersji i jeżeli pozwolisz mu zadzaiłać wg swoich algorytmów, to możesz dojść do błędów, które są trudne do zdebuggowania. Przedstawię tutaj kilka takich przykładów:

Źle:

DECLARE @smallValue smallint
SET @smallValue = 20000 + 20000
SELECT @smallValue

… w wyniku zwraca błąd.

Msg 220, Level 16, State 1, Line 3
Arithmetic overflow error for data type smallint, value = 40000.

Dlaczego?

Bo użyłeś typu smallint. Jeśli przewidujesz, że w obliczeniach może wyjść mniej niż -32 758 lub więcej niż 32 767 to lepiej użyj typu INT. Tinyint ma zakres od 0-255, int to już ponad 2 miliardy a bigint ponad 9 trylionów.

Jak poprawić ten przykład. Ot zmienić typ (więcej o typach i związanych z nimi przygodami znajdziesz na Mobilo / mobilo24 tutaj)?

DECLARE @smallValue int
SET @smallValue = 20000 + 20000
SELECT @smallValue

Źle:

DECLARE @date DATETIME
SET @date = '20110915 23:59:59.999′
SELECT @date

w wyniku zwraca 2011-09-16 00:00:00.000.

Dlaczego? Bo typ DATETIME zapamiętuje datę z dokładnością do 000 lub 004 lub 007  milisekundy. Czas z 999 milisekundami zostanie zaokrąglony do wartości najbardziej zbliżonej w tym przypadku do 000, ale pociąga to za sobą zmiane sekund, minut itd, dając w efekcie datę z kolejnego dnia.

Jak to poprawić? SQL 2008 wprowadza typ DATETIME2, który nie ma już tego problemu, dlatego

DECLARE @date DATETIME2
SET @date = '20110915 23:59:59.999′
SELECT @date

zwraca 2011-09-15 23:59:59.9990000

Źle:

SELECT CONVERT(varchar(6),GetDate(),112)

zwraca dzisiaj czyli 16 IX 2011 wartość 201109.

Dlaczego? Format 112 określa, że data ma być skonwertowana do daty z rokiem zapisanym przy użyciu 4 cyfr. czyli 20110916. Skoro jednak konwersja ma być dokonana na napis sześcioznakowy, to dwie ostatnie cyfry zostają obcięte i zostaje 201109.

Jak to poprawić?

SELECT CONVERT(varchar(8),GetDate(),112)

działa poprawnie, bo napis mieści się cały.

Źle:

SELECT 'Dzisiaj jest '+GetDate()

zwraca błąd:

Msg 241, Level 16, State 1, Line 1

Conversion failed when converting date and/or time from character string.

Dlaczego?

Bo dla SQL typ datetime jest tyem mocniejszym niż typy znakowe, a podczas łączenia różnych typów SQL serwer podejmuje decyzję o dokonaniu konwersji do mocniejszego typu. Dlatego zamiast intuicyjnej konwersji do napisu, podejmowana jest próba skonwertowania napisu do typu zgodnego z datą.

Jak to poprawić? Trzeba po prostu wyraźnie wskazać jaki rodzaj konwersji ma być wykonany:

SELECT 'Dzisiaj jest '+CONVERT(VARCHAR(10),GetDate(),104)

Zwraca Dzisiaj jest 16.09.2011.

Może znasz inne takie przykłady? Pochwal się nimi!

Komentarze są wyłączone