smalldatetime (Transact-SQL)smalldatetime (Transact-SQL)

適用於: 是SQL Server 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

定義與當日時間結合的日期。Defines a date that is combined with a time of day. 這個時間是以 24 小時制為基礎,而秒鐘一律為零 (:00) 而且不含小數秒數。The time is based on a 24-hour day, with seconds always zero (:00) and without fractional seconds.

注意

對新工作使用 timedatedatetime2datetimeoffset 資料類型。Use the time, date, datetime2 and datetimeoffset data types for new work. 這些類型符合 SQL 標準。These types align with the SQL Standard. 它們具有方便移植的特性。They are more portable. timedatetime2datetimeoffset 提供更多秒數有效位數。time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset 可為全域部署的應用程式提供時區支援。datetimeoffset provides time zone support for globally deployed applications.

smalldatetime 描述smalldatetime description

語法Syntax smalldatetimesmalldatetime
使用方式Usage DECLARE @MySmalldatetime smalldatetimeDECLARE @MySmalldatetime smalldatetime

CREATE TABLE Table1 ( Column1 smalldatetime )CREATE TABLE Table1 ( Column1 smalldatetime )
預設的字串常值格式Default string literal formats

(用於下層用戶端)(used for down-level client)
不適用Not applicable
日期範圍Date range 1900-01-01 到 2079-06-061900-01-01 through 2079-06-06

1900 年 1 月 1 日到 2079 年 6 月 6 日January 1, 1900, through June 6, 2079
時間範圍Time range 00:00:00 到 23:59:5900:00:00 through 23:59:59

2007-05-09 23:59:59 會四捨五入為2007-05-09 23:59:59 will round to

2007-05-10 00:00:002007-05-10 00:00:00
元素範圍Element ranges YYYY 是代表年份的四位數,範圍介於 1900 至 2079 之間。YYYY is four digits, ranging from 1900, to 2079, that represent a year.

MM 是代表指定年份中某個月份的兩位數,範圍介於 01 至 12 之間。MM is two digits, ranging from 01 to 12, that represent a month in the specified year.

DD 是代表指定月份中某個日期的兩位數,範圍介於 01 至 31 之間 (視月份而定)。DD is two digits, ranging from 01 to 31 depending on the month, that represent a day of the specified month.

hh 是代表小時的兩位數,範圍介於 00 至 23 之間。hh is two digits, ranging from 00 to 23, that represent the hour.

mm 是代表分鐘的兩位數,範圍介於 00 至 59 之間。mm is two digits, ranging from 00 to 59, that represent the minute.

ss 是代表秒鐘的兩位數,範圍介於 00 至 59 之間。ss is two digits, ranging from 00 to 59, that represent the second. 29.998 秒或以下值會無條件捨去到最接近的分鐘。Values that are 29.998 seconds or less are rounded down to the nearest minute. 29.999 秒或以上值會無條件進位到最接近的分鐘。Values of 29.999 seconds or more are rounded up to the nearest minute.
字元長度Character length 19 個位置上限19 positions maximum
儲存體大小Storage size 4 個位元組 (固定)。4 bytes, fixed.
精確度Accuracy 一分鐘One minute
預設值Default value 1900-01-01 00:00:001900-01-01 00:00:00
日曆Calendar 西曆Gregorian

(不含年份的完整範圍)。(Doesn't include the complete range of years.)
使用者自訂的小數秒數有效位數User-defined fractional second precision No
時區位移感知和保留Time zone offset aware and preservation No
日光節約感知Daylight saving aware No

ANSI 和 ISO 8601 標準ANSI and ISO 8601 Compliance

smalldatetime 不符合 ANSI 或 ISO 8601 標準。smalldatetime isn't ANSI or ISO 8601 compliant.

轉換日期和時間資料Converting date and time data

當您轉換成日期與時間資料類型時,SQL ServerSQL Server 會拒絕所有無法辨識為日期或時間的值。When you convert to date and time data types, SQL ServerSQL Server rejects all values it can't recognize as dates or times. 如需搭配日期和時間資料使用 CAST 及 CONVERT 函數的詳細資訊,請參閱 CAST 和 CONVERT (Transact-SQL)For information about using the CAST and CONVERT functions with date and time data, see CAST and CONVERT (Transact-SQL).

將 smalldatetime 轉換成其他日期與時間類型Converting smalldatetime to other date and time types

本節描述當 smalldatetime 資料類型轉換成其他日期和時間資料類型時,可能發生的狀況。This section describes what occurs when a smalldatetime data type is converted to other date and time data types.

如果轉換成 date,年、月和日都會複製。For a conversion to date, the year, month, and day are copied. 下列程式碼顯示將 smalldatetime 值轉換成 date 值的結果。The following code shows the results of converting a smalldatetime value to a date value.

DECLARE @smalldatetime smalldatetime = '1955-12-13 12:43:10';  
DECLARE @date date = @smalldatetime  
  
SELECT @smalldatetime AS '@smalldatetime', @date AS 'date';  
  
--Result  
--@smalldatetime          date  
------------------------- ----------  
--1955-12-13 12:43:00     1955-12-13  
--  
--(1 row(s) affected)  

當轉換成 time(n) 時,時、分和秒都會複製。When the conversion is to time(n), the hours, minutes, and seconds are copied. 小數秒數會設定為 0。The fractional seconds are set to 0. 下列程式碼顯示將 smalldatetime 值轉換成 time(4) 值的結果。The following code shows the results of converting a smalldatetime value to a time(4) value.

DECLARE @smalldatetime smalldatetime = '1955-12-13 12:43:10';  
DECLARE @time time(4) = @smalldatetime;  
  
SELECT @smalldatetime AS '@smalldatetime', @time AS 'time';  
  
--Result  
--@smalldatetime          time  
------------------------- -------------  
--1955-12-13 12:43:00     12:43:00.0000  
--  
--(1 row(s) affected)  

當轉換成 datetime 時,smalldatetime 值會複製到 datetime 值。When the conversion is to datetime, the smalldatetime value is copied to the datetime value. 小數秒數會設定為 0。The fractional seconds are set to 0. 下列程式碼顯示將 smalldatetime 值轉換成 datetime 值的結果。The following code shows the results of converting a smalldatetime value to a datetime value.

DECLARE @smalldatetime smalldatetime = '1955-12-13 12:43:10';  
DECLARE @datetime datetime = @smalldatetime;  
  
SELECT @smalldatetime AS '@smalldatetime', @datetime AS 'datetime';  
  
--Result  
--@smalldatetime          datetime  
------------------------- -----------------------  
--1955-12-13 12:43:00     1955-12-13 12:43:00.000  
--  
--(1 row(s) affected)  

如果轉換成 datetimeoffset(n)smalldatetime 值會複製到 datetimeoffset(n) 值。For a conversion to datetimeoffset(n), the smalldatetime value is copied to the datetimeoffset(n) value. 小數秒數會設定為 0,而時區時差會設定為 +00:0。The fractional seconds are set to 0, and the time zone offset is set to +00:0. 下列程式碼顯示將 smalldatetime 值轉換成 datetimeoffset(4) 值的結果。The following code shows the results of converting a smalldatetime value to a datetimeoffset(4) value.

DECLARE @smalldatetime smalldatetime = '1955-12-13 12:43:10';  
DECLARE @datetimeoffset datetimeoffset(4) = @smalldatetime;  
  
SELECT @smalldatetime AS '@smalldatetime', @datetimeoffset AS 'datetimeoffset(4)';  
  
--Result  
--@smalldatetime          datetimeoffset(4)  
------------------------- ------------------------------  
--1955-12-13 12:43:00     1955-12-13 12:43:00.0000 +00:0  
--  
--(1 row(s) affected)  

當轉換成 datetime2(n) 時,smalldatetime 值會複製到 datetime2(n) 值。For the conversion to datetime2(n), the smalldatetime value is copied to the datetime2(n) value. 小數秒數會設定為 0。The fractional seconds are set to 0. 下列程式碼顯示將 smalldatetime 值轉換成 datetime2(4) 值的結果。The following code shows the results of converting a smalldatetime value to a datetime2(4) value.

DECLARE @smalldatetime smalldatetime = '1955-12-13 12:43:10';  
DECLARE @datetime2 datetime2(4) = @smalldatetime;  
  
SELECT @smalldatetime AS '@smalldatetime', @datetime2 AS ' datetime2(4)';  
  
--Result  
--@smalldatetime           datetime2(4)  
------------------------- ------------------------  
--1955-12-13 12:43:00     1955-12-13 12:43:00.0000  
--  
--(1 row(s) affected)  

範例Examples

A.A. 將包含秒數的字串常值轉換成 smalldatetimeCasting string literals with seconds to smalldatetime

下列範例會比較將字串常值中的秒數轉換成 smalldatetime 的結果。The following example compares the conversion of seconds in string literals to smalldatetime.

SELECT   
     CAST('2007-05-08 12:35:29'     AS smalldatetime)  
    ,CAST('2007-05-08 12:35:30'     AS smalldatetime)  
    ,CAST('2007-05-08 12:59:59.998' AS smalldatetime);  
輸入Input 輸出Output
2007-05-08 12:35:292007-05-08 12:35:29 2007-05-08 12:35:002007-05-08 12:35:00
2007-05-08 12:35:302007-05-08 12:35:30 2007-05-08 12:36:002007-05-08 12:36:00
2007-05-08 12:59:59.9982007-05-08 12:59:59.998 2007-05-08 13:00:002007-05-08 13:00:00

B.B. 比較 date 和 time 資料類型Comparing date and time data types

下列範例會比較將字串轉換成各種 date 與 time 資料類型的結果。The following example compares the results of casting a string to each date and time data type.

SELECT   
     CAST('2007-05-08 12:35:29. 1234567 +12:15' AS time(7)) AS 'time'   
    ,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS date) AS 'date'   
    ,CAST('2007-05-08 12:35:29.123' AS smalldatetime) AS   
        'smalldatetime'   
    ,CAST('2007-05-08 12:35:29.123' AS datetime) AS 'datetime'   
    ,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS datetime2(7)) AS   
        'datetime2'  
    ,CAST('2007-05-08 12:35:29.1234567 +12:15' AS datetimeoffset(7)) AS   
        'datetimeoffset';  
資料類型Data type 輸出Output
timetime 12:35:29.12:35:29. 12345671234567
datedate 2007-05-082007-05-08
smalldatetimesmalldatetime 2007-05-08 12:35:002007-05-08 12:35:00
datetimedatetime 2007-05-08 12:35:29.1232007-05-08 12:35:29.123
datetime2datetime2 2007-05-08 12:35:29.2007-05-08 12:35:29. 12345671234567
datetimeoffsetdatetimeoffset 2007-05-08 12:35:29.1234567 +12:152007-05-08 12:35:29.1234567 +12:15

另請參閱See also

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