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

 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

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

執行後的查詢結果如下：

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

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

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

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

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