Filter by Dates in SQL
If your dates
column does not contain time information, you could get away with:
WHERE dates BETWEEN '20121211' and '20121213'
However, given your dates
column is actually datetime, you want this
WHERE dates >= '20121211'
AND dates < '20121214' -- i.e. 00:00 of the next day
Another option for SQL Server 2008 onwards that retains SARGability (ability to use index for good performance) is:
WHERE CAST(dates as date) BETWEEN '20121211' and '20121213'
Note: always use ISO-8601 format YYYYMMDD with SQL Server for unambiguous date literals.
Filter data based on date in sql
Since you have not mentioned about any errors, if START_DATE
and END_DATE
are DATETIME
data type, there is nothing wrong with your query. If you are not getting the correct records, Please check the data.
However your date format may trouble you in a different server
. There are some good practices you could adhere to avoid such issues.
-Whenever date is used as a string, try to use it in ISO or ISO8601
format (ie 'yyyymmdd' or 'yyyy-mm-ddThh:mi:ss.mmm')-Also avoid joining tables with WHERE Table1, Table2 which is old and
obsolete. JOINs are much better performed, neat and tidy.
You can change your query as follows;
SELECT DISTINCT T1.column1, T1.column2, T2.START_DATE, T2.END_DATE
FROM Table1 T1 JOIN Table2 T2 ON T1.column1 = T2.column2
WHERE (T2.START_DATE >= '20130115 10:58:58' AND
T2.END_DATE <= '20130118 10:58:58')
ORDER BY T2.START_DATE DESC
How can I filter on date values?
80 years old or older.
This means that they were born earlier than current-date - 80 years. Your comparison is in the wrong direction:
sqlQuery(con, "SELECT *
FROM People
WHERE BirthDT <= '1941-02-11'")
Date filtering from past two weeks
You can compare the created_on
column with an expression that calculates "two weeks before today":
un.created_on >= current_date - interval '2 weeks'
alternatively you can also subtract 14 days
un.created_on >= current_date - 14
How to filter date range from date - 2 weeks and date + 2 weeks
Try using the add_days()
function:
WHERE t.VALID_FROM >= add_days('2020-11-26', -14) AND
t.VALID_TO <= add_days('2020-11-26', -14)
properly filtering Dates in SQL
If touchedwhen
is a date/time, then use something like this:
(CV3OrderTaskOccurrence.TouchedWhen >= '2020-01-01' and
CV3OrderTaskOccurrence.TouchedWhen < '2021-01-01'
)
Actually, this will also work if the value is stored as a string, but I strongly recommend that you store values using correct data types.
How do I filter by date in SQL?
You must use single quotes in where
clause, double quotes are used in column identifier or reserved words.
select *
from table_name
where date_col = '2015-04-02'
limit 5
Related Topics
Why Are Batch Inserts/Updates Faster? How Do Batch Updates Work
SQL Server Join Tables and Pivot
How to Start Auto Increment from a Specific Point
MySQL How to Insert into [Temp Table] from [Stored Procedure]
Spark Replacement for Exists and In
Determine If One Coordinate Is in Radius of Another
SQL Select with Column Name Like
Check If a Parameter Is Null or Empty in a Stored Procedure
Like '%' Does Not Accept Null Value
How to Implement a Keyword Search in MySQL
Bigquery SQL for 28-Day Sliding Window Aggregate (Without Writing 28 Lines of SQL)
Why Don't Dbms's Support Assertion
Oracle: Updating a Table Column Using Rownum in Conjunction with Order by Clause
Hive - Unpivot Functionality in Hive
The Alter Table Statement Conflicted with the Foreign Key Constraint
Use Variable with Top in Select Statement in SQL Server Without Making It Dynamic
Update Rows in One Table with Data from Another Table Based on One Column in Each Being Equal