Recipe9.11.Searching on Specific Units of Time

Recipe 9.11. Searching on Specific Units of Time

Problem

You want to search for dates that match a given month, or day of the week, or some other unit of time. For example, you want to find all employees hired in February or December, as well as employees hired on a Tuesday.

Solution

Use the functions supplied by your RDBMS to find month and weekday names for dates. This particular recipe can be useful in various places. Consider, if you wanted to search HIREDATEs but wanted to ignore the year by extracting the month (or any other part of the HIREDATE you are interested in), you can do so. The example solutions to this problem search by month and weekday name. By studying the date formatting functions provided by your RDBMS, you can easily modify these solutions to search by year, quarter, combination of year and quarter, month and year combination, etc.

DB2 and MySQL

Use the functions MONTHNAME and DAYNAME to find the name of the month and weekday an employee was hired, respectively:

 1 select ename 2   from emp 3 where monthname(hiredate) in ('February','December') 4    or dayname(hiredate) = 'Tuesday' 

Oracle and PostgreSQL

Use the function TO_CHAR to find the names of the month and weekday an employee was hired. Use the function RTRIM to remove trailing whitespaces:

 1 select ename 2   from emp 3 where rtrim(to_char(hiredate,'month')) in ('february','december') 4    or rtrim(to_char(hiredate,'day')) = 'tuesday' 

SQL Server

Use the function DATENAME to find the names of the month and weekday an employee was hired:

 1 select ename 2   from emp 3 where datename(m,hiredate) in ('February','December') 4    or datename(dw,hiredate) = 'Tuesday' 

Discussion

The key to each solution is simply knowing which functions to use and how to use them. To verify what the return values are, put the functions in the SELECT clause and examine the output. Listed below is the result set for employees in DEPTNO 10 (using SQL Server syntax):

  select ename,datename(m,hiredate) mth,datename(dw,hiredate) dw   from emp  where deptno = 10 ENAME   MTH        DW ------  ---------  ----------- CLARK   June       Tuesday KING    November   Tuesday MILLER  January    Saturday 

Once you know what the function(s) return, finding rows using the functions shown in each of the solutions is easy.