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

Source: Internet
Author: User
Tags iso 8601

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.