Hướng dẫn học Microsoft SQL Server 2008 part 26

Có hai loại hỗ trợ: Ngày giờ nhỏ hơn và DateTime2. DateTime2 là mới cho SQL Server 2008 và đại diện cho thời gian đến một độ chi tiết tốt hơn nhiều: trong vòng 100 nano giây. | Part II Manipulating Data With Select RESULT SELECT LastName DATENAME AS Year DATENAME mm DateOfBirth AS Month DATENAME dd DateOfBirth AS Day DATENAME weekday DateOfBirth AS BirthDay FROM WHERE DateOfBirth IS NOT NULL LastName Year Month Day BirthDay Frank 1958 September 4 Thursday TABLE 9-2 DateTime Portions Used by Date Functions Portion Abbreviation year yy yyyy quarter qq q month mm m dayofyear dy d day dd d week wk ww weekday dw hour hh minute mi n second ss s millisecond ms microsecond mcs nanosecond ns TZoffset tz There are two supported types DateTime and DateTime2. DateTime2 is new to SQL Server 2008 and represents time to a much finer granularity within 100 nanoseconds. DatePart date porti on date Returns the ordinal number of the selected portion of the datetime value. The following example retrieves the day of the year and the day of the week as integers 212 Data Types Expressions and Scalar Functions 9 SELECT DATEPART dayofyear CURRENT_TIMESTAMP AS DayCount Result DayCount 321 SELECT DATEPART weekday CURRENT_TIMESTAMP AS DayWeek Result DayWeek 7 An easy way to obtain just the date stripping off the time is to use a couple of string functions SELECT C0NVERT char 10 CURRENT_TIMESTAMP 112 AS DateTime DateAdd DATE PORTION AMOUNT BEGINNING DATE and DateDiff DATE PORTION BEGINNING DATE ENDING DATE Performs addition and subtraction on datetime data which databases often need to do. The DATEDIFF and the DATEADD functions are designed expressly for this purpose. The DATEDIFF doesn t look at the complete date only the date part being extracted select DATEDIFF year september 4 2008 november 10 2009 Result 1 select DATEDIFF month september 4 2008 november 10 2009 2 The following query calculates the number of years and days that my wife Melissa and I have been married SELECT DATEDIFF yy 19840520 CURRENT_TIMESTAMP AS MarriedYears DATEDIFF dd 19840520 CURRENT_TIMESTAMP AS MarriedDays Result MarriedYears MarriedDays 17 .