Date/Time Data – A summary of the date/time functions
In the topics that follow, you’ll learn how to use some of the functions SQL Server provides for working with dates and times. As you’ll see, these include functions for extracting different parts of a date/time value and for performing operations on dates and times. In addition, you’ll learn how to perform different types of searches on date/time values.
Table 1 presents a summary of the date/time functions and shows how some of them work. One of the functions you’ll use frequently is GETDATE(), which gets the current local date and time from your system. GETUTCDATE() is similar, but it returns the Universal Time Coordinate (UTC) date, also known as Greenwich Mean Time (GMT).
Although you probably won’t use the GETUTCDATE() function often, it’s useful if your system will operate in different time zones. That way, the date/time values will always reflect Greenwich Mean Time, regardless of the time zone in which they’re entered. For example, a date/time value entered at 11:00 a.m. Los Angeles time would be given the same value as a date/time value entered at 2:00 p.m. New York time. That makes it easy to compare and operate on these values.
The next three functions (SYSDATETIME(), SYSUTCDATETIME(), and SYSDATETIMEOFFSET()) work similarly to the first two functions. However, they return the datetime2 and datetimeoffset types that were introduced with SQL Server 2008. As a result, these functions return a more precise fractional second value. In addition, the SYSDATETIMEOFFSET function returns a value that includes a time zone offset. Note that the time zone offset is not adjusted for daylight savings time.
The next five functions (DAY, MONTH, YEAR, DATENAME, and DATEPART) let you extract different parts of a date value. For more information about these functions, you can refer to tables in the next section “”. For now, just realize that when you use the DATEPART and DATENAME functions, you can retrieve any of the date parts listed in Table 3.
The DATEADD and DATEDIFF functions let you perform addition and subtraction operations on date/time values. As you can see, these functions let you specify the date part to be added. For more information about these functions, you can refer to tables in the next section “How to Parse Dates and Times“.
The TODATETIMEOFFSET and SWITCHOFFSET functions let you work with the datetimeoffset data type. In particular, you can use the TODATETIMEOFFSET function to add a time zone offset to a datetime2 value and return a datetimeoffset value. In addition, you can use the SWITCHOFFSET function to specify a new time zone offset value for a datetimeoffset value.
The next two functions, EOMONTH and DATEFROMPARTS, are new with SQL Server 2012. The EOMONTH function gets the last day of the month for the specified date. This can be helpful for determining what days are valid for a given month. The DATEFROMPARTS function lets you create a date value for a given year, month, and day. In addition to this function, SQL Server 2012 provides other functions that let you create datetime, smalldatetime, time, datetime2, and datetimeoffset values.
The last function (ISDATE) returns a Boolean value that indicates whether an expression can be cast as a valid date/time value. This function is useful for testing the validity of a date/time value before it’s saved to a table. This is illustrated by the last set of examples. Here, you can see that the first and third expressions are valid dates, but the second and fourth expressions aren’t. The second expression isn’t valid because the month of September has only 30 days. And the fourth expression isn’t valid because a time value can have a maximum of 59 minutes and 59 seconds. Note that this function checks for both a valid date/time format and a valid date/time value.
The first two sets of examples illustrate the differences between the functions that return date/time values. To start, there’s a 7 hour difference between the datetime value that’s returned by the GETDATE and GETUTCDATE functions. That’s because I ran these functions from California, which is 7 hours behind the Universal Time Coordinate (UTC). In addition, note that the datetime2(7) value that’s returned by the SYSDATETIME function provides more precise fractional second values than the datetime value that’s returned by the GETDATE and GETUTCDATE functions. Finally, note that the SYSDATETIMEOFFSET function returns a datetimeoffset value that includes a time zone offset.
The third set of examples shows how you can use the date parts with the DATEPART and DATENAME functions. To start, you don’t need to specify a date part when you use the MONTH function to return an integer value for the month. However, you can get the same result with the DATEPART function by specifying the month date part as the first argument. Or, if you want to return the name of the month as a string of characters, you can specify the month date part as the first argument of the DATENAME function. Finally, you can use an abbreviation for a date part whenever that makes sense. However, I generally prefer to avoid abbreviations as they tend to make the code more difficult to read and understand.
The fourth set of examples shows how to use two of the new date/time functions. Here, the first expression uses the EOMONTH function to return a date for the last day of the month for February 1, 2012. Since 2012 is a leap year, this returns February 29, 2012. The second expression is similar, but it adds two months to the specified date. Finally, the last expression uses the DATEFROMPARTS function to create a date with a year value of 2012, a month value of 4, and a day value of 3.
Table 1 – Some of the date/time functions
Function
|
Description
|
GETDATE ()
|
Returns a datetime value for the current local date and time based on the system's clock
|
GETUTCDATE ()
|
Returns a datetime value for the current UTC date and time based on the system's clock and time zone setting
|
SYSDATETIME ()
|
Returns a datetime2(7) value for the current local date and time based on the system's clock
|
SYSUTCDATETIME ()
|
Returns a datetime2(7) value for the current UTC date and time based on the system's clock and time zone setting
|
SYSDATETIMEOFFSET ()
|
Returns a datetimeoffset(7) value for the current UTC date and time based on the system's clock and time zone setting with a time zone offset that is not adjusted for daylight savings time
|
DAY (date)
|
Returns the day of the month as an integer
|
MONTH ( date)
|
Returns the month as an integer
|
YEAR (date)
|
Returns the 4-digit year as an integer
|
DATENAME(datepart, date)
|
Returns the part of the date specified by datepart as a character string
|
DATEPART(datepart, date)
|
Returns the part of the date specified by datepart as an integer
|
DATEADD (datepart, number, date)
|
Returns the date that results from adding the specified number of datepart units to the date
|
DATEDIFF (datepart, startdate, enddate)
|
Returns the number of datepart units between the specified start and end dates.
|
TODATETIMEOFFSET (datetime2, tzoffset)
|
Returns a datetimeoffset value that results from adding the specified time zone offset to the specified datetime2 value
|
SWITCHOFFSET (datetimeoffset, tzoffset)
|
Returns a datetimeoffset value that results from switching the time zone offset for the specified datetimeoffset value to the specified offset
|
EOMONTH(startdate [,months])
|
Returns a date for the last day of the month specified by the start date. If months is specified, the number of months is added to the start date before the end-of-month date is calculated
|
DATEFROMPARTS (year, month, day)
|
Returns a date for the specified year, month, and day
|
ISDATE (expression)
|
Returns a value of 1 (true) if the expression is a valid datettime value; returns a value of 0 (false) otherwise
|
Table 2 – Date part values and abbreviations
Arguments
|
Abbreviations
|
year
|
yy, yyyy
|
quarter
|
qq, q
|
month
|
mm, m
|
dayofyear
|
dy, y
|
day
|
dd, d
|
week
|
wk, ww
|
weekday
|
dw
|
hour
|
hh
|
minute
|
mi, n
|
second
|
ss, s
|
millisecond
|
ms
|
microsecond
|
mcs
|
nanosecond
|
ns
|
tzoffset
|
tz
|
Table 3 – Examples that use date/time functions
Function
|
Result
|
GETDATE()
|
2012-09-30 14:10:13.813
|
GETUTCDATE()
|
2012-09-30 21:10:13.813
|
SYSDATETIME()
|
2012-09-30 14:10:13.8160822
|
SYSUTCDATETIME()
|
2012-09-30 21:10:13.8160822
|
SYSDATETIMEOFFSET()
|
2012-09-30 14:10:13.8160822 -07.00
|
MONTH('2012-09-30')
|
9
|
DATEPART(month,'2012-09-30')
|
9
|
DATENAME(month,'2012-09-30')
|
September
|
DATENAME(m,'2012-09-30')
|
September
|
EOMONTH('2012-02-01')
|
2012-02-29
|
EOMONTH('2012-02-01',2)
|
2012-04-30
|
DATEFROMPARTS(2012,4,3)
|
2012-04-03
|
ISDATE('2012-09-30')
|
1
|
ISDATE('2012-09-31')
|
0
|
ISDATE('23:59:59')
|
1
|
ISDATE('23:99:99')
|
0
|
No comments:
Post a Comment