192
Part II: Building Databases and Working with Data
T-SQL includes several functions to return the current date and time:■ GetDate(): Returns the current server date and time to the nearest 3 1/3 millisec-
onds, rounded to the nearest value.
■ CURRENT_TIMESTAMP: The same as GETDATE() except ANSI standard.■ (^) GetUTCDate(): Returns the current server date converted to Greenwich mean
time (also known as UTC time) to the nearest 3 milliseconds. This is extremely use-
ful for companies that cross time boundaries.
■ SysDateTime(): Returns the current server date and time to the nearest hundred
nanoseconds.
■ SysUTCDateTime(): Returns the current server date converted to Greenwich
mean time to the nearest hundred nanoseconds.
■ SYSDATETIMEOFFSET(): Returns a DateTimeOffset value that contains the
date and time of the computer on which the instance of SQL Server is running. The
time zone offset is included.
■ (^) ToDateTimeOffset(): Returns a DateTimeOffset type.
The following four SQL Server date-time functions handle extracting or working with a spe-
cifi c portion of the date or time stored within a datetime column:
■ DATEADD(date portion, number, date): Returns a new value after adding the
number
■ DATEDIFF(date portion, start date, end date): Returns the count of the
date portion boundaries
■ DateName(date portion, date): Returns the proper name for the selected
portion of the datetime value or its ordinal number if the selected portion has no
name (the portions for DateName() and DatePart() are listed in Table 8-6):
SELECT DATENAME(year, CURRENT_TIMESTAMP) AS 'Year';
Result:
Year
2009
TABLE 8- 6 DateTime Portions Used by Date Functions
Portion Abbreviation
Year yy, yyyy
quarter qq, q
month mm, m
dayofyear dy, d
c08.indd 192c08.indd 192 7/30/2012 4:21:15 PM7/30/2012 4:21:15 PM
http://www.it-ebooks.info