Manipulate and Calculate

It might take a while for you to fully grasp how to enter, store, and display datetime data, but knowing how to effectively use dates and times to meet your business needs makes the effort worthwhile. I discussed the basics of datetime data types in "The Datetime Dilemma," June 2002, and its sidebar "Just in Time." However, you also need to know how to access and manipulate the components of this datetime data and perform datetime math. For example, what if you need to assemble a guest list of customers 21 years of age or older for your grand-opening gala, compute a 6-month (or 180-day) warranty date for a purchase; or send a customer letter about warranties that are due to expire in 30 days? And how do you return a message to a client application with a well-formatted and somewhat verbose date such as "This order was placed on the 7th day of April, 2002."? This month's column answers all these questions and more. 

The easiest way to access date and time data in SQL Server is to use the built-in date- and time-related system functions, which are simple scalar functions. These functions are the most basic of all functions because they return only one value—regardless of the number of parameters passed in. (SQL Server 2000 also lets you create user-defined functions—UDFs—which include scalar functions. In an upcoming column, I'll discuss how you can use scalar UDFs to help you reuse complex datetime expressions.)

SQL Server offers nine built-in scalar functions for accessing date and time data. GETDATE() and GETUTCDATE() return the current date and time. DATEPART() and DATENAME() help create well-formatted results. DAY(), MONTH(), and YEAR() simplify access to the common components of datetime data by minimizing the number of parameters you need. And DATEADD() and DATEDIFF() let you perform datetime math by manipulating the individual components of a datetime value.

GETDATE() and GETUTCDATE() are the two most frequently used datetime functions because they return the current time at the server. GETDATE() returns the local time at the server, and GETUTCDATE() returns the Universal Time Coordinate (UTC)—also called Greenwich Mean Time (GMT)—based on certain Windows server settings. To return the correct date and time, you must make sure that your server's date and time-zone information is properly set in Control Panel. As an interesting benefit, you can use these functions within a DEFAULT CONSTRAINT on your table's definition so that an insert's date and time is recorded for historical or auditing purposes. Listing 1's code sample creates a DEFAULT CONSTRAINT that uses the GETUTCDATE() function as the default for tracking record insertion.

The DATEPART() and DATENAME() functions help pull components from the datetime values so that you can build your own custom datetime information and produce the results you need. With a few exceptions, which I discuss later, these functions provide almost identical information for many parameters but in different forms. The DATEPART() function returns an integer, whereas the DATENAME() function returns a string. (Sometimes returning a string can be helpful when you're building custom messages based on date and time values.) SQL Server not only provides these flexible functions for returning different forms of the same values, but it also offers synonym functions for accessing common parameters for date values. Although the DAY(), MONTH(), and YEAR() standard synonyms have fewer parameters than DATEPART(), they return the same values (i.e., they return an integer). You can run the code in Listing 2, page 10, to execute and view the DATENAME() and DATEPART() functions with all their parameters alongside the applicable DAY(), MONTH(), or YEAR() synonym function where appropriate.

Table 1, page 11, shows Listing 2's output slightly altered to highlight the differences between the DATEPART() and DATENAME() functions and to illustrate where the functions return different values. The table's italicized values highlight the month and weekday date components, which always produce different values for these two functions. DATEPART() returns the month as a numeric equivalent—such as 04—and DATENAME() returns the month as a text-based value—such as April. Session and server settings determine the bolded values. First, the year component can be affected by the server setting for the two digit year cutoff configuration option, which defines the cutoff date for assigning the century value (when not supplied on insert) of 19 or 20. As I recommended in my June 2002 column, always specify four-digit year values to avoid ambiguity. Second, the weekday value that the DATEPART() function returns is based on the day you've chosen to be the first day of the week as well as on the session's LANGUAGE setting, as I explain later. The DATEFIRST session setting defines the first day of the week, assigning a value between 1 and 7, where Monday equals 1 and Sunday equals 7. By default, DATEFIRST is set to 7, meaning that Sunday is considered the first day of the week. If you ask for DATEPART(weekday, 'April 7, 2002'), the function returns 1. If you issue the SET DATEFIRST 2 command to change the DATEFIRST value so you can assign Tuesday as the first day of the week, DATEPART(weekday, 'April 7, 2002') returns 6. Moreover, if you change the first day of the week to Tuesday, you'll probably also change the week value that DATEPART(week, 'April 7, 2002') returns. Let's look at an example for this scenario.

The code in Listing 3 changes the session setting DATEFIRST to 2, meaning that the first day of the week is Tuesday instead of the default Sunday. The output then displays the date range from Saturday, April 6, 2002, through Tuesday, April 9, 2002, demonstrating the differences in the way DATEPART() and DATENAME() work and the results they return. DATENAME() defines a new week as beginning on Sunday and therefore returns the week as week 15. However, because you changed the first day of the week to Tuesday, DATEPART() considers Sunday, April 7, 2002, part of week 14 (because January 1, 2002, was a Tuesday) and considers Tuesday, April 9, 2002, week 15 because Tuesday is the first day of a new week. Note that DATENAME() isn't affected by the DATEFIRST setting and always bases the week value with a starting date of Sunday. So, for consistent week values that won't be affected by session settings, consider using DATENAME(). The LANGUAGE setting also influences DATENAME()'s month and weekday date components. Listing 4, page 12, shows examples of how SET LANGUAGE changes the results that DATENAME() returns. For example, when you set LANGUAGE to French, Avril (French for April) is returned for April and dimanche (French for Sunday) is returned for Sunday. Also, notice how the language setting changes date input. To enter the date of April 6, 2002, you must use Avril 6, 2002.

Now that you've reviewed the information that each of the date functions returns, you're ready to use it creatively. Earlier, I mentioned a scenario in which you might want to return a message to a client application and receive a verbose response such as "This order was placed on the 7th day of April, 2002." To build part of the string, you can use DAY(), DATENAME(), and DATEPART()—along with a CASE expression—to piece together the date components. However, one point to remember about string concatenation is that to build your message, you must convert to a string any integer that these date functions return. Conversely, you can often use DATENAME() to return the date component as a string and possibly avoid using the CAST() or CONVERT() functions. Your choice depends primarily on the value that you want to return. For example, if you want the numeric value returned for month, you must use DATEPART(), then convert the value to a string. However, the day of the month will be the same whether you use DATENAME() or DATEPART(). For example, for the 10th day of the month, DATEPART() returns 10 as an integer, and DATENAME() returns 10 as a string. The code in Listing 5, page 12, shows two options for displaying the verbose message: The first option uses DATEPART() with CONVERT() (or CAST()), and the second option uses DATENAME()—the better option in this case because you have fewer conversions to make.

The remaining two built-in datetime functions—DATEADD() and DATEDIFF()—let you perform calculations on your datetime values. DATEADD() is useful when you want to add or subtract an integer value from a date while maintaining every component of the date. DATEDIFF() lets you compare only one date component between two dates. For example, suppose you want to create a guest list of all customers who will be 21 or older by September 1, 2002, the date of a grand-opening party where alcohol will be served. Like many other developers in similar situations, you might mistakenly use DATEDIFF(), as Listing 6, page 13, shows. Developers often think that DATEDIFF() returns the difference between two complete dates, when in fact it returns only a difference between the two values of the component you choose. When you review the data, you'll find that most of the customers will be 21 by the party date. However, when you review Listing 6's output, you see that even customers who were born in late September or November of 1981 are showing up as 21, based on the DATEDIFF() result. This result isn't the one you want, but it's correct for DATEDIFF(), which performs the calculation by dropping all components other than the one requested (in this case, year). In asking for DATEDIFF() in years between the grand-opening date and a customer's birth date, you told SQL Server to return the difference between the year components of these dates. For example, CustomerID 7 has a birthday of November 1, 1981, so SQL Server calculates the difference between 2002 and 1981, which is 21. In effect, DATEDIFF() returns the number of datetime boundaries crossed only between the requested component of the two dates supplied.

If you want to retrieve the appropriate customers for the grand-opening event (i.e., see only those customers who are actually 21—the accurate completion of 21, including months and days, by the date of the event), you need to use DATEADD(). The grand opening's exact date and time is September 1, 2002, at 7:00 pm, so to be eligible to attend, a customer needs to have a birth date of September 1, 1981, or earlier. You could easily hard-code this date and time into the query's WHERE clause, but the goal of this exercise is to programmatically determine whether a customer should attend. So in this case, let's use a variable to track the grand-opening date. For a more permanent solution, this variable would be a parameter within a stored procedure.

The first step is to use the grand-opening date and subtract 21 years, so you can use the following code to create a variable to store the grand-opening date:

DECLARE @GrandOpeningdatetimeSET @GrandOpening = 'Sep 1, 2002 7:00PM'SELECT DATEADD(Year, -21, @GrandOpening)

This query returns September 1, 1981, at 7:00 pm, but you need to disregard the time because any customer born on that date is eligible to attend. The easiest way to eliminate time is to change the format to one that doesn't include the time component. However, when you eliminate time, it never really goes away. Instead, when you use the datetime and smalldatetime datatypes, by default the time is set to midnight. In this case, a time of midnight would cause a problem. If you say that a customer has to be born on a date and time before September 1, 1981, at midnight, you are wrongly eliminating customers who were born on September 1, 1981. In this scenario, to attend the gala and drink alcohol, a customer has to be born on or before the date 21 years earlier—in this case, midnight of September 2, 1981. So what you're looking for is the grand-opening date minus 21 years plus one day, with the time component removed. A nice feature of all datetime data types is that when you add an integer to the date, the date component that you add the integer to always defaults to the day component. Adding the integer gives you the equivalent of DATEADD(day, integer, date). In this case, you can easily add one day:

SELECT DATEADD(Year, -21, @GrandOpening + 1)

Next, you need to remove the time to return a date to compare to your table's birth date column. Make sure you convert to a datetime data type that returns a result matching your client's DATEFORMAT setting or the ISO standard format—style 112. By default, the session setting for DATEFORMAT is mdy. However, if you use a format that doesn't result in mdy, your code might appear to work even though the dates are incorrect. For example, if you use style 104 (dmy), your code would return February 9, 2002, instead of September 2, 2002. Style 101 works when DATEFORMAT is set to mdy, and style 112 works regardless of style setting. In general, you want to use style 112 to produce intermediate results—results that will continue to be computed or used in other formulas. That way, you can ensure that your month and day components are never ambiguous. The following code snippet uses style 112:

SELECT CONVERT(varchar(10), DATEADD(Year, -21, @GrandOpening + 1), 112)

Now, you're ready to use this expression in the WHERE clause to compare against customers' birth dates. The following query looks for all customers whose birthdays come before September 2, 1981, at midnight:

SELECT CustomerID, BirthdateFROM dbo.CustomerWHERE Birthdate 

Using Scalar Functions Creatively

An understanding of data type storage (in "Datetime Dilemma") and the effects of client and server settings on datetime data sends you well on your way to inviting only the right customers to your grand openings. By applying the strategies I've demonstrated in this two-part series, you'll be able to create accurate datetime conversions and produce the correct results. In an upcoming column, I'll discuss creating user-defined scalar functions to reuse datetime common expressions.