SQL Server時間粒紋系列----第1節時間粒紋概述

標籤：

本文目錄列表： 1、什麼是時間粒紋?
2、SQL Server提供的時間粒紋
3、SQL Server時間粒紋代碼示範   4、SQL Server基準日期 5、總結語
6、參考清單列表  什麼是時間粒紋?       我們知道比如年、季度、月、旬（一個月分為上中下3旬）、日這樣的時間單位；也知道小時、分鐘、秒；也有短於秒的比如毫秒、微妙、納秒、飛秒等等時間單位。基於不同的時間，我們可以將時間粒紋看作不同時間單位的時間值。     舉個例子來說明時間粒紋這個問題。一個日期時間值是‘2016-02-29‘，很顯然我們知道這個日期所對應的年、月、日分別為2016年、2月和29日，這個29日就是2016年2月第29天（日）。我們也可以繼續根據這個日期時間值發問以下的問題：此日期屬於2016年第幾周？此日期屬於所在周的星期幾？此日期屬於2016年2月的那個旬？此日期屬於2016年的第幾季度？此日期 又屬於所在季度的第幾天？此日期屬於2016年的上半年還是下半年？此日期屬於2016年的第幾天？……        有些公司會有自己公司特殊的財務處理周期時間段，比如說：4個月、4個月、3個月為不同時間段的財務處理周期。  SQL Server提供的時間粒紋      我們來看看SQL Server提供了那些日期時間資料類型，smalldatetime和datetime（SQL Server 2005提供的），後來在SQL Server 2008又新增了date、time、datetime2和datetimeoffset共4個資料類型。以上6種日期時間資料類型用法和區別請參考 日期和時間資料類型及函數 (Transact-SQL)。        SQL Server針對這些日期時間資料類型的具體一個日期時間值都能得到那些時間粒紋呢？我們可以通過datepart函數（https://msdn.microsoft.com/zh-cn/library/ms189794(v=sql.90).aspx和https://msdn.microsoft.com/zh-cn/library/ms174420(v=sql.100).aspx）可以知道。下面的表格匯總了datepart函數將獲得指定日期時間值的哪些時間粒紋值：

日期部分	縮寫	SQL Server運行版本
year	yy,yyyy	SQL Server 2005 +
quarter	qq,q	SQL Server 2005 +
month	mm,m	SQL Server 2005 +
dayofyear	dy,y	SQL Server 2005 +
day	dd,d	SQL Server 2005 +
week	wk,ww	SQL Server 2005 +
weekday	dw	SQL Server 2005+
hour	hh	SQL Server 2005 +
minute	mi,n	SQL Server 2005 +
second	ss,s	SQL Server 2005 +
millisecond	ms	SQL Server 2005 +
microsecond	mcs	SQL Server 2008 +
nanosecond	ns	SQL Server 2008 +
TZoffset	tz	SQL Server 2008 +
ISO_WEEK	isowk,isoww	SQL Server 2008 +

注意：SQL Server 2005 +表示的意思是：SQL Server 2005以及SQL Server 2005以上版本。   SQL Server時間粒紋代碼示範    1、日期以上時間粒紋的T-SQL代碼如下：

 1 -- 日期以上時間粒紋的T-SQL代碼： 2 DECLARE    @dtmCreateTime AS DATETIME; 3 SET @dtmCreateTime = ‘2016-02-29 23:59:59.997‘; 4 SELECT DATEPART(YEAR, @dtmCreateTime) AS ‘Year,yy,yyyy CurrentYear‘; 5 SELECT DATEPART(QUARTER, @dtmCreateTime) AS ‘Quarter,qq,q CurrentQuarter‘; 6 SELECT DATEPART(MONTH, @dtmCreateTime) AS ‘Month,mm,m CurrentMonth‘; 7 SELECT DATEPART(DAYOFYEAR, @dtmCreateTime) AS ‘DayOfYear,dy,y DayOfCurrentYear‘; 8 SELECT DATEPART(DAY, @dtmCreateTime) AS ‘Day,dd,d DayOfCurrentMonth‘; 9 SELECT DATEPART(WEEK, @dtmCreateTime) AS ‘Week,wk,ww WeekOfCurrentYear‘, @@DATEFIRST AS ‘FisrtDayOfWeek[可以為1到7，sunday對應的7]‘, DATEPART(WEEKDAY, @dtmCreateTime) AS ‘Weekday,dw WeekdayOfCurrentWeek‘, DATENAME(WEEKDAY, @dtmCreateTime) AS ‘WeekdayName‘;10 GO

 

執行後的查詢結果如下： 2、時間以下的時間粒紋的T-SQL代碼：

1 -- 時間以下時間粒紋的T-SQL代碼：2 DECLARE    @dtmCreateTime AS DATETIME;3 SET @dtmCreateTime = ‘2016-02-29 23:59:59.997‘;4 SELECT DATEPART(HOUR, @dtmCreateTime) AS ‘Hour,hh HourOfDay‘, DATEPART(MINUTE, @dtmCreateTime) AS ‘Minute,mi,n MinuteOfDay‘, DATEPART(SECOND, @dtmCreateTime) AS ‘Second,ss,s SecondOfDay‘,  DATEPART(MILLISECOND, @dtmCreateTime) AS ‘Millisecond,ms MillisecondOfDay‘;5 SELECT DATEPART(MICROSECOND, @dtmCreateTime) AS ‘Mircosecond,mcs MicrosecondOfDay‘, DATEPART(NANOSECOND, @dtmCreateTime) AS ‘Nanosecond,ns NanosecondOfDay‘;6 GO

 

執行後的查詢結果如下：

  3、時區位移TZOFFSET時間粒紋的T-SQL代碼：

1 -- 時區位移TZOFFSET時間粒紋的T-SQL代碼：2 DECLARE @dtmofsDateTime AS DATETIMEOFFSET;3 SET @dtmofsDateTime = ‘2007-05-10  00:00:01.1234567 +05:10‘4 SELECT DATEPART (TZOFFSET, @dtmofsDateTime) AS ‘TZoffset,tz TZoffsetMinuteTotal‘;5 GO

 

執行後的查詢結果如下： 4、 ISO_WEEK時間粒紋的T-SQL代碼：

1 -- ISO_WEEK時間粒紋的T-SQL代碼:2 SELECT DATEPART(ISO_WEEK, ‘2016-01-06‘) AS ‘ISO_WEEK,isowk,isoww ISO_WeekValue‘3 GO

 

執行後的查詢結果如下：  注意：以下文字摘抄自SQL Server線上說明，具體請參考：https://msdn.microsoft.com/zh-cn/library/ms174420(v=sql.100).aspx

ISO 8601 包括 ISO 周-日期系統，即周的編號系統。每周都與該周內星期四所在的年份關聯。例如，2004 年第 1 周 (2004W01) 從 2003 年 12 月 29 日星期一到 2004 年 1 月 4 日星期天。一年中最大的周數可能為 52 或 53。這種編號方式通常用於歐洲省/地區，但其他省/地區很少用到。

不同的省/地區的編號系統可能不符合 ISO 標準。現在至少可能存在六種編號系統，如下表所示：

每周的第一天	一年的第一周包含	分配兩次的周	使用的省/地區
星期日	1 月 1 日， 第一個星期六， 其中有 1–7 天屬於此年	是	美國
星期一	1 月 1 日， 第一個星期日， 其中有 1–7 天屬於此年	是	大多數歐洲國家和英國
星期一	1 月 4 日， 第一個星期四， 其中有 4-7 天屬於此年	否	ISO 8601，挪威和瑞典
星期一	1 月 7 日， 第一個星期一， 7 天均屬於此年	否
星期三	1 月 1 日， 第一個星期二， 其中有 1–7 天屬於此年	是
星期六	1 月 1 日， 第一個星期五， 其中有 1–7 天屬於此年	是

  SQL Server基準日期
      我們執行下面的T-SQL代碼：

1 SELECT DATEPART(YEAR, 0) AS ‘BaseDateYear‘, DATEPART(MONTH, 0) AS ‘BaseDateMonth‘, DATEPART(DAY, 0) AS ‘BaseDateDay‘;2 SELECT YEAR(0) AS ‘BaseDateYear‘, MONTH(0) AS ‘BaseDateMonth‘, DAY(0) AS ‘BaseDateDay‘;3 GO

 

執行後的查詢結果如下：     從上面的查詢結果我們可以知道datepart(year, @dtmDateTime)、datepart(month, @dtmDateTime)、datepart(day, @dtmDateTime)分別與year(@dtmDateTime)、month(@dtmDateTime)、day(@dtmDateTime)獲得的結果值相同。這個日期“1900-01-01”就是SQL Server提供的基準日期（SQL Server線上說明成為基準年）。有關這個基準日期的一種特殊用法，就是資料表設計的一種方法消除NULL，將欄位設定為NOT NULL加預設值。針對日期時間資料類型欄位列的預設值就可以設定為這個“1900-01-01”基準日期。     SQL Server提供的datetime日期時間資料類型，內部使用兩個4位元組的整數來儲存該資料值。第一個4位元組儲存”基準日期“（即1900年1月1日）之前或之後的天數。基準日期是系統參照日期。另一個4位元組儲存天的時間，以午夜後經過的1/300秒數表示。SQL Server引擎也是以“1900-01-01”這個基準日期來參考使用的。具體的請參考日期和時間 (Transact-SQL)。     後面的博文會更多的使用這個基準日期的。      總結語          我們在本文中瞭解了有關時間粒紋的概述，還有SQL Server提供了那些類型的時間粒紋，以及各個時間粒紋的T-SQL代碼示範，最後我們知道SQL Server 提供了一個基準日期（基準年）“1900-01-01“。  參考清單列表1、http://baike.baidu.com/link?url=EwLlbVIkgcS3qxQCCdGgM5LfKjufP0-EHyUUOpRyrNoBONePh2NzJszzQvMGQrZOHJ9f8SPA6iBcwk93uR4_OK2、https://msdn.microsoft.com/zh-cn/library/ms174420(v=sql.90).aspx3、https://msdn.microsoft.com/zh-cn/library/ms174420(v=sql.100).aspx4、https://msdn.microsoft.com/zh-cn/library/ms186724(v=sql.100).aspx5、https://msdn.microsoft.com/zh-cn/library/ms174395(v=sql.90).aspx6、https://msdn.microsoft.com/zh-cn/library/ms187819(v=sql.90).aspx

 

SQL Server時間粒紋系列----第1節時間粒紋概述