ISDATE (Transact-SQL)ISDATE (Transact-SQL)

適用於: 是SQL Server (從 2008 開始) 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

expression 為有效的 datetimedatetime,則傳回 1;否則傳回 0。Returns 1 if the expression is a valid date, time, or datetime value; otherwise, 0.

ISDATE 會在 expressiondatetime2 值時傳回 0。ISDATE returns 0 if the expression is a datetime2 value.

如需所有 Transact-SQLTransact-SQL 日期和時間資料類型與函數的概觀,請參閱日期和時間資料類型與函數 (Transact-SQL)For an overview of all Transact-SQLTransact-SQL date and time data types and functions, see Date and Time Data Types and Functions (Transact-SQL). 請注意,datetime 資料範圍為 1753-01-01 到 9999-12-31,而 date 資料範圍是 0001-01-01 到 9999-12-31。Note that the range for datetime data is 1753-01-01 through 9999-12-31, while the range for date data is 0001-01-01 through 9999-12-31.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

ISDATE ( expression )  

引數Arguments

expressionexpression
為字元字串,或可以轉換為字元字串的運算式Is a character string or expression that can be converted to a character string. 此運算式的長度必須少於 4,000 個字元。The expression must be less than 4,000 characters. 日期和時間資料類型 (但不包括 datetime 和 smalldatetime) 不允許做為 ISDATE 的引數。Date and time data types, except datetime and smalldatetime, are not allowed as the argument for ISDATE.

傳回類型Return Type

intint

RemarksRemarks

只有在搭配 CONVERT 函式使用,且已指定 CONVERT 樣式參數,並且樣式不等於 0、100、9 或 109 時,ISDATE 才具確定性。ISDATE is deterministic only if you use it with the CONVERT function, if the CONVERT style parameter is specified, and style is not equal to 0, 100, 9, or 109.

ISDATE 的傳回值會根據 SET DATEFORMATSET LANGUAGE設定預設語言伺服器設定選項的設定而不同。The return value of ISDATE depends on the settings set by SET DATEFORMAT, SET LANGUAGE and Configure the default language Server Configuration Option.

ISDATE 運算式格式ISDATE expression Formats

如需 ISDATE 將傳回 1 之有效格式的範例,請參閱 datetimesmalldatetime 主題中的<datetime 支援的字串常值格式>一節。For examples of valid formats for which ISDATE will return 1, see the section "Supported String Literal Formats for datetime" in the datetime and smalldatetime topics. 如需其他範例,請參閱 CAST 和 CONVERT 中<引數>一節內的輸入/輸出資料行。For additional examples, also see the Input/Output column of the "Arguments" section of CAST and CONVERT.

下表將摘要列出無效而且會傳回 0 或錯誤的輸入運算式格式。The following table summarizes input expression formats that are not valid and that return 0 or an error.

ISDATE 運算式ISDATE expression ISDATE 傳回值ISDATE return value
NULLNULL 00
資料類型的任何資料類型類別中所列的資料類型值,但字元字串、Unicode 字元字串或日期和時間除外。Values of data types listed in Data Types in any data type category other than character strings, Unicode character strings, or date and time. 00
textntextimage 資料類型的值。Values of text, ntext, or image data types. 00
秒數有效位數超過 3 的任何值 (.0000 到 .0000000...n)。Any value that has a seconds precision scale greater than 3, (.0000 through .0000000...n). expressiondatetime2 值,則 ISDATE 會傳回 0;若 expression 為有效的 datetime 值,則會傳回 1。ISDATE will return 0 if the expression is a datetime2 value, but will return 1 if the expression is a valid datetime value. 00
混合有效日期與無效值的任何值,例如 1995-10-1a。Any value that mixes a valid date with an invalid value, for example 1995-10-1a. 00

範例Examples

A.A. 使用 ISDATE 測試有效的 datetime 運算式Using ISDATE to test for a valid datetime expression

下列範例示範如何使用 ISDATE 來測試字元字串是否為有效的 datetimeThe following example shows you how to use ISDATE to test whether a character string is a valid datetime.

IF ISDATE('2009-05-12 10:19:41.177') = 1  
    PRINT 'VALID'  
ELSE  
    PRINT 'INVALID';  

B.B. 說明 SET DATEFORMAT 和 SET LANGUAGE 設定對傳回值的影響Showing the effects of the SET DATEFORMAT and SET LANGUAGE settings on return values

下列陳述式會說明傳回成為 SET DATEFORMATSET LANGUAGE 設定結果的值。The following statements show the values that are returned as a result of the settings of SET DATEFORMAT and SET LANGUAGE.

/* Use these sessions settings. */  
SET LANGUAGE us_english;  
SET DATEFORMAT mdy;  
/* Expression in mdy dateformat */  
SELECT ISDATE('04/15/2008'); --Returns 1.  
/* Expression in mdy dateformat */  
SELECT ISDATE('04-15-2008'); --Returns 1.   
/* Expression in mdy dateformat */  
SELECT ISDATE('04.15.2008'); --Returns 1.   
/* Expression in myd  dateformat */  
SELECT ISDATE('04/2008/15'); --Returns 1.  
  
SET DATEFORMAT mdy;  
SELECT ISDATE('15/04/2008'); --Returns 0.  
SET DATEFORMAT mdy;  
SELECT ISDATE('15/2008/04'); --Returns 0.  
SET DATEFORMAT mdy;  
SELECT ISDATE('2008/15/04'); --Returns 0.  
SET DATEFORMAT mdy;  
SELECT ISDATE('2008/04/15'); --Returns 1.  
  
SET DATEFORMAT dmy;  
SELECT ISDATE('15/04/2008'); --Returns 1.  
SET DATEFORMAT dym;  
SELECT ISDATE('15/2008/04'); --Returns 1.  
SET DATEFORMAT ydm;  
SELECT ISDATE('2008/15/04'); --Returns 1.  
SET DATEFORMAT ymd;  
SELECT ISDATE('2008/04/15'); --Returns 1.  
  
SET LANGUAGE English;  
SELECT ISDATE('15/04/2008'); --Returns 0.  
SET LANGUAGE Hungarian;  
SELECT ISDATE('15/2008/04'); --Returns 0.  
SET LANGUAGE Swedish;  
SELECT ISDATE('2008/15/04'); --Returns 0.  
SET LANGUAGE Italian;  
SELECT ISDATE('2008/04/15'); --Returns 1.  
  
/* Return to these sessions settings. */  
SET LANGUAGE us_english;  
SET DATEFORMAT mdy;  

範例:Azure SQL 資料倉儲Azure SQL Data Warehouse平行處理資料倉儲Parallel Data WarehouseExamples: Azure SQL 資料倉儲Azure SQL Data Warehouse and 平行處理資料倉儲Parallel Data Warehouse

C.C. 使用 ISDATE 測試有效的 datetime 運算式Using ISDATE to test for a valid datetime expression

下列範例示範如何使用 ISDATE 來測試字元字串是否為有效的 datetimeThe following example shows you how to use ISDATE to test whether a character string is a valid datetime.

IF ISDATE('2009-05-12 10:19:41.177') = 1  
    SELECT 'VALID';  
ELSE  
    SELECT 'INVALID';  

另請參閱See Also

CAST 和 CONVERT (Transact-SQL)CAST and CONVERT (Transact-SQL)