SQL Compare Date Without Time | A Practical Guide

Comparing dates without times is a common need when working with SQL databases. Luckily there are several methods and built-in functions to compare dates in SQL without factoring in the time portion But sometimes you just want to focus on the date component for your queries and analysis. 

This guide will talk about various techniques to compare dates in SQL while ignoring the time element.

CAST to Date Method for Precision

One of the simplest techniques is to cast your datetime column to the date data type:

SELECT * 
FROM sales
WHERE CAST(order_date AS DATE) = '2023-01-15'

This extracts just the date part, allowing for direct date comparisons.

TRUNCATE Time Part Method

Similar to casting, you can truncate the time element from a datetime with TRUNC:

SELECT *
FROM sales  
WHERE TRUNC(order_date) = '2023-01-15'

This removes the hours, minutes, seconds, and milliseconds.

Range Comparisons for Full-Day Analysis

Another option is to define a range for the full day:

SELECT *
FROM sales
WHERE order_date >= '2023-01-15' AND
      order_date < '2023-01-16'

The range comparison captures all records between the start and end of the day.

Time Zone Conversion for Consistency

When working across time zones, convert dates to a standard zone:

SELECT *  
FROM sales
WHERE CONVERT_TZ(order_date, 'UTC', 'Central Standard Time')  
     >= '2023-01-15' AND 
     CONVERT_TZ(order_date, 'UTC', 'Central Standard Time')
     < '2023-01-16'

This enables consistent date-only comparisons.

Extract the Date Part for Focused Comparisons

You can also extract just the date component:

SELECT *
FROM sales
WHERE EXTRACT(DAY FROM order_date) = 15 AND 
      EXTRACT(MONTH FROM order_date) = 1 AND
      EXTRACT(YEAR FROM order_date) = 2023

This breaks the date into its constituent parts for focused comparisons.

Date Truncation for Focused Comparisons

Another option is truncating to the date level:

SELECT * 
FROM sales
WHERE DATE_TRUNC('day', order_date) = '2023-01-15'

This truncates the datetime down to just the date portion.

Rounding or Flooring for Extracting Elements

For greater control, you can also round or floor the time element:

SELECT *
FROM sales  
WHERE ROUND(order_date, 0) = '20230115'

Rounding or flooring to a whole day level removes the unwanted time component.

Date Functions for Efficiency

Certain SQL date functions like DAY() and YEAR() extract date elements:

SELECT *
FROM sales
WHERE DAY(order_date) = 15 AND  
      MONTH(order_date) = 1 AND
      YEAR(order_date) = 2023

You can then compare each piece separately.

Secondary Filtering Method

An alternative approach is to filter in a sub-query:

SELECT *
FROM
  (SELECT *
   FROM sales
   WHERE DATE(order_date) = '2023-01-15') filtered_sales

This extracts just the matching date records before the main query.

Join on Date for Seamless Table Integration

You can also join tables on a date field without time:

SELECT *
FROM sales s
JOIN promos p
  ON DATE(s.order_date) = p.promo_date

Joining on the date-only portion can be helpful for certain analyses.

CTE Filtering for Enhanced Processing

A CTE can apply date filtering prior to the main query:

WITH sales_date AS
  (SELECT * 
   FROM sales
   WHERE DATE(order_date) = '2023-01-15')
SELECT *
FROM sales_date

This encapsulates the date filtering before further processing.

Date Table Join for Standardized Comparisons

Maintaining a standalone date table enables easy joins:

SELECT s.*  
FROM sales s
JOIN dim_date d
  ON d.date = CAST(s.order_date AS DATE)
WHERE d.date = '2023-01-15'

Joining on a dedicated date table standardizes the comparisons.

Derived Table for Enhanced Syntax

You can use a derived table to simplify the syntax:

SELECT *
FROM
 (SELECT order_date,
         CAST(order_date AS DATE) AS order_day
  FROM sales) dt
WHERE order_day = '2023-01-15'

This avoids repeating the CAST logic.

Stored Procedure for Reusable Date Filtering

Encapsulate the date filtering in a reusable stored procedure:

CREATE PROC sales_by_date
  @order_date DATE  
AS
SELECT *
FROM sales 
WHERE CAST(order_date AS DATE) = @order_date

Then pass the desired dates to filter each query.

View Abstracting for Streamlined Queries

Similarly, you can create a view abstracting the datetime filtering:

CREATE VIEW sales_date_view AS
SELECT *,
  CAST(order_date AS DATE) AS order_day
FROM sales

Then query the view filtering just on order_day.

FAQs – Frequently Asked Questions and Answers

1. Is it possible to index a date column without time?

Answer: Yes, you can create indexes on date columns, which enables faster searches on the date portion.

2. What about outer join on just the date part?

Answer: Outer joins work similarly – you can join or filter in the ON clause based on the date-only column.

3. Is it better to filter before or after the join?

Answer: It depends! Benchmark both options and see which performs better for your data volumes and use case.

To Conclude

Mastering the art of comparing dates without time in SQL empowers you to unlock deeper insights, optimize your queries, and reduce complexities. The key is finding the right approach for your specific data volumes, use case and database platform. Try different methods and compare execution plans to optimize performance.