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 @GrandOpening datetime
SET @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, Birthdate
FROM dbo.Customer
WHERE Birthdate < CONVERT(varchar(10),
DATEADD(Year, -21, @GrandOpening + 1), 112)
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.
End of Article
Prev. page
1
[2]
next page -->
