Sidebar

How to compare dates in an SQL query without the time (get all records for a date ignoring time)?

+2 votes
218.7K views
asked Jul 4, 2013 by rich-c-2789 (16,120 points)
How can I drop the time from a datetime field in my query so that I can get all records for a date.  The time portion is blocking me from getting all records for a date.
commented Jul 9, 2013 by rich-c-2789 (16,120 points)
need to validate these queries, date types, date formats on the specific systems

1 Answer

+1 vote
 
Best answer

There are several ways to write a query using dates where you need to find all records matching an given date without using the time.  Some are more vendor neutral.  With each there are performance, readability, and other issues for choosing one over another.  This answer will not go into those details or list all possibilities.  For that you can google to learn more.  So, here are a few ways to deal with datetimes, timestamps or etc that have a time when you only care about whether the date matches:

Examples will select new products added on June 1st 2013 by specifying a condition that checks a createdDate column.  The data type for the createdDate column is defined as a timestamp or datetime (depending on the system):

Example 1 (Range: date format maybe vendor dependent):

SELECT productName, productDesc FROM Products WHERE createdDate >= '2013-06-01' and createdDate < '2013-06-02' 

Example 2 (SQL-92 Cast: data type maybe vendor dependent.  Query can be transported to any system If using a data type defined in the same SQL spec support by the database):

SELECT productName, productDesc FROM Products WHERE CAST(createdDate AS DATE) = '2013-06-01'

Example 3 (MS SQL Convert: vendor dependent but other databases may support this):

SELECT productName, productDesc FROM Products WHERE Convert(DATE, createdDate) = '2013-06-01'

Example 4 (MySQL Date: vendor dependent but other databases may support this):

SELECT productName, productDesc FROM Products WHERE Date(createdDate) = '2013-06-01'

Example 5 (Oracle/Postgres Trunc: vendor dependent but other databases may support this):

SELECT productName, productDesc FROM Products WHERE Trunc(createdDate) = '2013-06-01'

Hopefully this will give you some idea on what to google.

answered Jul 9, 2013 by rich-c-2789 (16,120 points)
selected Jul 9, 2013 by mike-r-7535
...