Date Functions

There are only five date functions, but those five do a lot. You know that you can search for dates using LIKE, which converts dates to strings and then searches those strings. The date functions demonstrated in this section will help you work further with dates. In contrast to LIKE, date functions return date values, not strings.

Most of the date functions take an argument (the first argument) to tell it on what part of the date to operate. Table 4.3 shows the different dateparts available for use in the date functions.

You can refer back to this chart while working with the examples that follow. When providing arguments to these functions, do not use quotes around the dateparts, but do use quotes around the dates themselves.

GETDATE()

This is one function I find myself using all the time, in lots of different applications. GETDATE() returns the current date and time as a datetime value:

select   getdate()

--------------------------
Dec 5 1996 10:03AM

It is most often used in stored procedures and triggers to test validity of an operation. For example, an inserted row might be tested to see whether a planned test run row was more than seven days from now or whether an appointment time had already passed (that is, if column > getdate() then {complain…}).

DATEADD (<datepart>, <increment>, <source>)

Adds a number of dateparts to a date. To ask for today’s date plus thirty days, this query first uses GETDATE() to get the current date and time and then uses DATEADD to add thirty days. Note the use of dd as the datepart argument.

select  dateadd(dd,  30,  getdate())

--------------------------
Jan 4 1997 10:06AM

DATEDIFF(<datepart>, <date1>, <date2>)

Calculates the difference between date1 and date2, expressed in dateparts. This query determines the number of days left until Christmas:

select datediff(dd, getdate(), “12/25/96”) “Xmas Countdown”

Xmas Countdown
--------------
20

If the second date is earlier than the first date, you will receive a negative result.

DATEPART(<datepart>, <source date>)

Extracts the specified datepart from the source date. DATEPART returns an integer; DATENAME returns a variable character string. This query sums the sales of books in May versus the sales of books in December, across all years:

select       sum(qty) “Summer Sales”
from         sales
where        datepart(mm, ord_date) = 5
select       sum(qty) “Christmas Sales”
from         sales
where        datepart(mm, ord_date) = 12

Summer Sales
------------
165

(1 row(s) affected)

Christmas Sales
--------------
10

---

Warning:  I mentioned this earlier in the chapter, but it bears repeating: if you use a function in the WHERE clause of a query, indexes cannot be used to resolve the query. This could cause the response time for your query to go from five seconds to five hours on a large table.

Avoid using functions in the WHERE clause of a query whenever possible.

---

DATENAME(<datepart>, <source date>)

DATENAME returns a part of the date, like datepart, but expresses it as a string. It is most useful for days of the week and months of the year. This query shows how to get the day of the week from today’s date:

select       “Today is “ + datename(dw, getdate()) + ‘.’

------------------------
Today is Thursday.

System Functions

The server has a collection of functions that return system information. These functions are useful in application programs to retrieve state information about the server, the connection, and the user logged in on the connection.

I won’t describe all of the system functions here, just the ones that are the most useful.

DATALENGTH(<source>)

DATALENGTH returns the length, in bytes, of the source. It accepts any data type. If it is passed a null, DATALENGTH returns null as the data length, even though a null really does take up space in a row.

For single-byte character sets, DATALENGTH will return the number of characters in a string. For multibyte character sets (such as Unicode and traditional Chinese), where two bytes are required to represent each character, DATALENGTH returns the bytecount for the string, equal to twice the number of characters.

To get all but the first character of a title, which is a variable-length character field, I would use this query:

select   right(title, datalength(title) - 1)
from     titles

ut Is It User Friendly?
omputer Phobic AND Non-Phobic Individuals: Behavior Variations
ooking with Computers: Surreptitious Balance Sheets
motional Security: A New Algorithm
[…]

Fields such as money, int, smallint, tinyint, datetime, are all fixed-length. DATALENGTH will return the same byte length for these data types any time they contain data, no matter what the value.