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

Last update on December 20 2024 11:37:35 (UTC/GMT +8 hours)

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

Sample table: HumanResources.EmployeePayHistory

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.