Query:
SELECT fname, lname, hire_date FROM EMPLOYEES WHERE CAST(CAST(YEAR(GETDATE()) AS varchar(4))+ SUBSTRING(CONVERT(char(8), hire_date,112),5,4) AS datetime) BETWEEN GETDATE() AND GETDATE()+30
Function: Attempts to determine hire date anniversaries for all employees that fall within the next thirty days (the anniversaries, not the employees). Starting with line 3, this query extracts the current year and casts it as a varchar(4). Then in the following line, the query converts each hire_date value to style 112 (which is yyyymmdd) and then trims out the mmdd as a substring (start at character 5 and use the 4 characters that follow) and casts it as datetime (the outer cast statement of the preceding line). So it constructs a date from the hire_date column which would be the annual hire anniversary for each employee and then only selects it for display in the query results if it falls between the current date and 30 days hence. The synthesized date might not be valid, however, because the employee might have been hired in a leap year.
Source: Henderson, Ken. The Guru’s Guide to Transact-SQL. Reading, MA: Addison-Wesley, 2000. Print. (p. 25)
Comments: Though we might be tempted to lose patience with Mr. Henderson for presenting us with a flawed query, we’re rewarded–should we read on–with a noble attempt to build gender equality through words: “If the employee was hired in a leap year and the current year isn’t also a leap year, you’ll have a problem if her hire date was February 29″ [emphasis mine]. I’ll bet you chauvinistically assumed this hypothetical employee was male, didn’t you !?! Admit it. You just don’t much care for the third-person singular feminine possessive pronoun … and how it watches Lifetime Network, prefers white wine to beer, and talks openly about its feelings. Well, Ken Henderson’s prose hearkens that THE TIMES THEY ARE A CHANGIN’, pal. And so on and so forth. Yeah ….