SQL Server time granularity series----section 1th time granularity overview

List of directories in this document: 1, what is the granularity of time?
2. The granularity of time provided by SQL Server
3. SQL Server time granularity code demo 4. SQL Server Base Date 5. Summary language
6. List of reference lists What is the granularity of time?  We know such as the year, quarter, month, lent (one months into the upper and lower 3), the time unit of the day, also know the hours, minutes, seconds, but also shorter than seconds such as milliseconds, subtle, nanosecond, femtosecond, and so on time units.     Depending on the time, we can consider time granularity as a time value for different time units. Give an example to illustrate the problem of time granularity. A datetime value is ' 2016-02-29 ', it is clear that we know that the date corresponds to the year, month, and Day respectively 2016, February and 29th, this 29th is the 29th day of February 2016 (day). We can also continue to ask the following question according to the date-time value: What week is this date in 2016? What day of the week does this date belong to? This date belongs to the day of February 2016? What quarter does this date belong to in the year 2016? What day of the quarter does this date belong to? Does this date belong to the first half or the second half of 2016 years? The day of the year 2016?        ...... Some companies have their own company special financial processing cycle time period, such as: 4 months, 4 months, 3 months for different time periods of the financial processing cycle. time granularity provided by SQL Server  Let's take a look at SQL Server that provides those datetime data types, smalldatetime and datetime (provided by SQL Server 2005), and later in SQL Server 2008 adds a date, time, DateTime2 and DateTimeOffset A total of 4 data types. Please refer to the above 6 date and time data type usage and differences Date and time data types and functions (Transact-SQL). What is the time granularity that SQL Server can get for a specific datetime value for these datetime data types? We can pass the DatePart function (https://msdn.microsoft.com/zh-cn/library/ms189794 (v=sql.90). aspx and https://msdn.microsoft.com/ zh-cn/library/ms174420 (v=sql.100). aspx) can be known. The following table summarizes which time granularity values the DatePart function will get for a specified datetime value:

Date part	Abbreviation	SQL Server Run version
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 +

Note SQL Server 2005 + means SQL Server 2005 and SQL Server 2005 or later.   SQL Server time granularity code demo  1, the time granularity above the T-SQL code is as follows:

1--The T-size of the time above the dateSQL code:2 DECLARE @dtmCreateTime as DATETIME;3SET @dtmCreateTime ='2016-02-29 23:59:59.997';4SELECT DATEPART (year, @dtmCreateTime) as'year,yy,yyyy CurrentYear';5SELECT DATEPART (QUARTER, @dtmCreateTime) as'quarter,qq,q Currentquarter';6SELECT DATEPART (MONTH, @dtmCreateTime) as'month,mm,m Currentmonth';7SELECT DATEPART (DayOfYear, @dtmCreateTime) as'dayofyear,dy,y dayofcurrentyear';8SELECT DATEPART (Day, @dtmCreateTime) as'day,dd,d Dayofcurrentmonth';9SELECT DATEPART (WEEK, @dtmCreateTime) as'WEEK,WK,WW weekofcurrentyear', @ @DATEFIRST as'fisrtdayofweek[can be 1 to 7,sunday corresponding to 7]', DATEPART (WEEKDAY, @dtmCreateTime) as'WEEKDAY,DW Weekdayofcurrentweek', Datename (WEEKDAY, @dtmCreateTime) as'WeekdayName';TenGO

The results of the query are as follows: 2, time-granular T-SQL code:

1--The time-granularity of the T-SQL code:2 DECLARE @dtmCreateTime as DATETIME;3SET @dtmCreateTime ='2016-02-29 23:59:59.997';4SELECT 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';5SELECT DATEPART (microsecond, @dtmCreateTime) as'Mircosecond,mcs Microsecondofday', DATEPART (nanosecond, @dtmCreateTime) as'Nanosecond,ns Nanosecondofday';6GO

The following query results are executed:

3. Time zone offset tzoffset T-SQL code with granularity:

1 --timezone offset tzoffset time granularity T-SQL code:2DECLARE @dtmofsDateTime as DATETIMEOFFSET; 3 ' 2007-05-10  00:00:01.1234567 +05:10'4'Tzoffset,tz Tzoffsetminutetotal'; 5 GO

The results of the query are as follows: 4, Iso_week time granularity T-SQL code:

1 --Iso_weektime granularity T-SQL code:2'2016-01-06'iso_ Week,isowk,isoww iso_weekvalue'3 GO

The following query results are executed: Note: The following text is extracted from the SQL Server online Help, please refer to: https://msdn.microsoft.com/zh-cn/library/ms174420 (v=sql.100). aspx

ISO 8601 includes the ISO week-date system, which is the week numbering system. Each week is associated with the year Thursday in the week. For example, the 1th Week of 2004 (2004W01) is from Monday December 29, 2003 to January 4, 2004 Sunday. The maximum number of weeks in a year may be 52 or 53. This numbering method is commonly used in European countries, but is rarely used in other countries/regions.

Numbering systems in different countries may not conform to the ISO standard. There may be at least six numbering systems now, as shown in the following table:

The first day of the week	The first week of the year contains	Week assigned two times	Country/region of use
Sunday	January The first of Saturday, Which have 1–7 days belong to this year	Is	United States
Monday	January First Sunday, Which have 1–7 days belong to this year	Is	Most European countries and the United Kingdom
Monday	January 4, The first of Thursday, 4-7 days of which belong to this year	Whether	ISO 8601, Norway and Sweden
Monday	January 7, The first of Monday, 7 days are in this year	Whether
Wednesday	January The first of Tuesday, Which have 1–7 days belong to this year	Is
Saturday	January The first of Friday, Which have 1–7 days belong to this year	Is

SQL Server Base date
     We execute the following T-SQL code:

1SELECT DATEPART (year,0) as'Basedateyear', DATEPART (MONTH,0) as'Basedatemonth', DATEPART (Day,0) as'Basedateday';2SELECT Year (0) as'Basedateyear', MONTH (0) as'Basedatemonth', Day (0) as'Basedateday';3GO

The results of the executed query are as follows: from the above query results we can know DatePart (year, @dtmDateTime), DATEPART (month, @dtmDateTime), DatePart (day, @dtmDateTime) The result values obtained are the same as the year (@dtmDateTime), month (@dtmDateTime), Day (@dtmDateTime) respectively. This date "1900-01-01" is the base date provided by SQL Server (SQL Server online Help becomes the base year). A special usage of this base date is a method of data table design that eliminates null and sets the field to NOT NULL plus the default value.     The default value for the date-time Data Type field column can be set to this "1900-01-01" base date. SQL Server provides a datetime date-time data type that internally uses two 4-byte integers to store the data value. The first 4 bytes stores the number of days before or after the base date (that is, January 1, 1900). The base date is the system reference date. Another 4-byte storage day, expressed as a 1/300-second number after midnight. The SQL Server engine is also referenced using the benchmark date "1900-01-01".     Please refer to date and time (Transact-SQL) for details. Later posts will use this base date more. Summary Language      we learned about the granularity of time in this article, as well as the time granularity that SQL Server provides for those types, and the T-SQL code demos for each time granularity, and finally we know that SQL Server provides a base date (base year) of "1900-01-01". List of reference lists1, 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 time granularity series----section 1th time granularity overview