搜尋本站文章

2015-12-30

日期時間資料類型:ISNULL函數、IS [NOT] NULL 述詞,對時間值是 NULL 時的處理方式是不同的



SQL Server 的日期時間類型,例如有:datetimeoffset、datetime2、smalldatetime、datetime等。

若輸入資料值時,僅輸入日期值,卻未提供時間值時,系統自動以 1900-01-01 00:00:00.000,午夜零點零分零秒的方式來呈現。

這對 ISNULL函數、IS [NOT] NULL 述詞,在處理這類資料值時,就會有不同的行為。




請參考以下的範例:

USE tempdb
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[atb01]') AND type in (N'U'))
 BEGIN
  DROP TABLE [dbo].[atb01]
  CREATE TABLE [dbo].[atb01](
 [Sid] [int] PRIMARY KEY,
 [Aircode] [varchar](50) NULL,
 [Edate] [datetime] NULL)
 END
ELSE 
BEGIN
 CREATE TABLE [dbo].[atb01](
 [Sid] [int] PRIMARY KEY,
 [Aircode] [varchar](50) NULL,
 [Edate] datetime NULL)
END
GO

-- 新增四筆資料列
INSERT atb01 VALUES(1, 'A201',GETDATE())
INSERT atb01 VALUES(2, 'A202', '1900/01/01') -- 只給日期,未給時間。資料類型:[datetime]。
INSERT atb01(Sid, Aircode) VALUES(3,'A203')
INSERT atb01(Sid, Aircode) VALUES(4,'A204')
GO

-- 00 有 4 筆資料列
/*
已知:第二筆資料列,Edate資料行的資料類型是:[datetime]。
只輸入日期,未給時間。

查詢時,可觀察到,系統自動以 1900-01-01 00:00:00.000,午夜零點零分零秒的方式來呈現。
由於未提供時間值,仍是 NULL。
*/

SELECT Sid, Aircode, Edate FROM atb01



- 01_範例資料表,有4筆資料列







01 使用 ISNULL() 函數,3 筆資料列

-- 01 使用 ISNULL() 函數,3 筆資料列




/*
ISNULL 函數:以指定的取代值來取代 NULL。

使用 ISNULL() 函數,將會抓取到  3 筆資料列,包含:兩筆未提供任何值,以及一筆僅提供日期值的資料列。
*/

SELECT Sid, Aircode, Edate FROM atb01
WHERE ISNULL(Edate,'') = ''


-- 02_使用 ISNULL() 函數,抓到3筆資料列






02 使用 IS NULL 述詞,2 筆資料列



-- 02 使用 IS NULL 述詞,2 筆資料列 
/*

IS [NOT] NULL 述詞:判斷指定的運算式是否為 NULL。
若使用 IS [NOT] NULL 述詞來判斷 Edate 資料行是否為 NULL,將會抓取到 2 筆資料列。
*/

SELECT Sid, Aircode, Edate FROM atb01
WHERE Edate IS NULL



-- 03_IS [NOT] NULL 述詞,抓到2筆資料列






有些專案在設計時,若沒有輸入

1. 若沒有輸入日期時間時,前端程式將自動輸入 1900/01/01。
2. 後續接手的人員,可能改變此規則,改採取不輸入,讓系統存放 NULL 值,這又會是另一套規則。

或許,在某些狀況下, NULL 與 1900/01/01,對於此專案是具相同的意義。
但使用 ISNULL 函數與 IS NULL 述詞,卻是取得不同的結果。

若能修改原始資料值,具備一致的特性,則在 WHERE 條件式就可以精簡許多,也有機會可以使用索引來提升查詢效能。




ISNULL 函數

ISNULL 函數:以指定的取代值來取代 NULL。

語法:
ISNULL ( check_expression , replacement_value )

check_expression
這是要檢查 NULL 的運算式。check_expression 可以是任何類型。

replacement_value
這是 check_expression 是 NULL 時所傳回的運算式。replacement_value 必須是能夠隱含地轉換成 check_expresssion 類型的類型。




IS [NOT] NULL 述詞

判斷指定的運算式是否為 NULL。

語法:
expression IS [ NOT ] NULL

引數

expression
這是任何有效的運算式。

NOT
指定執行布林結果的否定運算。 這個述詞會反轉它的傳回值,如果值不是 NULL,就傳回 TRUE,如果值是 NULL,就傳回 FALSE。

結果類型 Boolean

傳回碼值
如果 expression 的值是 NULL,IS NULL 會傳回 TRUE;否則,它會傳回 FALSE。
如果 expression 的值是 NULL,IS NOT NULL 會傳回 FALSE;否則,它會傳回 TRUE。

備註
若要判斷運算式是否為 NULL,請利用 IS NULL 或 IS NOT NULL 來取代比較運算子 (如 = 或 !=)。
當兩個引數或其中一個引數是 NULL 時,比較運算子會傳回 UNKNOWN。



參考資料

SQL Server:認識 ISNULL 函數
http://sharedderrick.blogspot.tw/2012/06/t-sql-isnull.html

ISNULL (Transact-SQL)
https://msdn.microsoft.com/zh-tw/library/ms184325(v=sql.120).aspx

資料類型 (Transact-SQL)
https://msdn.microsoft.com/zh-tw/library/ms187752(v=sql.120).aspx

IS [NOT] NULL (Transact-SQL)
https://msdn.microsoft.com/zh-tw/library/ms188795(v=sql.120).aspx