AdventureWorks Database: Retrieve rows whose datetime values are between '20111212' and '20120105'

SQL Query - AdventureWorks: Exercise-144 with Solution

144. From the follwing table write a query in SQL to retrieve rows whose datetime values are between '20111212' and '20120105'.

businessentityid|ratechangedate         |rate   |payfrequency|modifieddate           |
----------------+-----------------------+-------+------------+-----------------------+
               1|2009-01-14 00:00:00.000|  125.5|           2|2014-06-30 00:00:00.000|
               2|2008-01-31 00:00:00.000|63.4615|           2|2014-06-30 00:00:00.000|
               3|2007-11-11 00:00:00.000|43.2692|           2|2014-06-30 00:00:00.000|
               4|2007-12-05 00:00:00.000|   8.62|           2|2007-11-21 00:00:00.000|
               4|2010-05-31 00:00:00.000|  23.72|           2|2010-05-16 00:00:00.000|
               4|2011-12-15 00:00:00.000|29.8462|           2|2011-12-01 00:00:00.000|
               5|2008-01-06 00:00:00.000|32.6923|           2|2014-06-30 00:00:00.000|
               6|2008-01-24 00:00:00.000|32.6923|           2|2014-06-30 00:00:00.000|
               7|2009-02-08 00:00:00.000|50.4808|           2|2014-06-30 00:00:00.000|
               8|2008-12-29 00:00:00.000|40.8654|           2|2014-06-30 00:00:00.000|
			   -- more --

Click to view Full table

Sample Solution:

-- Selecting BusinessEntityID and RateChangeDate columns from the EmployeePayHistory table for records with RateChangeDate falling between '20111212' and '20120105'
SELECT 
    -- Selecting the BusinessEntityID column from the EmployeePayHistory table
    BusinessEntityID, 
    -- Selecting the RateChangeDate column from the EmployeePayHistory table
    RateChangeDate  
-- Selecting data from the EmployeePayHistory table
FROM 
    HumanResources.EmployeePayHistory  
-- Filtering records where RateChangeDate falls between '20111212' and '20120105'
WHERE 
    RateChangeDate BETWEEN '20111212' AND '20120105';

Explanation:

• This SQL code retrieves information about BusinessEntityID and RateChangeDate from the EmployeePayHistory table for records where the RateChangeDate falls between '20111212' and '20120105'.
• The SELECT statement specifies the columns to be included in the result set.
• The WHERE clause filters records to include only those where the RateChangeDate falls within the specified date range.
• The result set will contain columns for BusinessEntityID and RateChangeDate for records meeting the specified criteria.

Sample Output:

businessentityid|ratechangedate         |
----------------+-----------------------+
               4|2011-12-15 00:00:00.000|
             224|2012-01-01 00:00:00.000|

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Find all rows outside a specified range of rate.
Next: Returns TRUE even if NULL is specified in the subquery.