Avatar of dstjohnjr
dstjohnjrFlag for United States of America asked on

SQL Query to filter records between a date range

I am looking to create a SQL query that will return all records in a table between two date ranges (using the date fields in the table in the database).  In my table are two date fields:

1)  StartDate
2)  EndDate

If StartDate is before today, return the record in the results
If EndDate is after today, return the record in the results

TIA for any assistance!
Microsoft SQL ServerMicrosoft SQL Server 2008SQL

Avatar of undefined
Last Comment
dstjohnjr

8/22/2022 - Mon
appari

try

Select * from yourtableName
where
convert(varchar,startdate,112) <= convert(varchar,getdate(),112)
or convert(varchar,enddate,112) >= convert(varchar,getdate(),112)
ASKER CERTIFIED SOLUTION
appari

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Roger Baklund

Try this:
select * from TableName
where curdate() between StartDate and EndDate

Open in new window

nmcdermaid

What datatype are StartDate and EndDate? Are they DATETIME or VARCHAR?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER
dstjohnjr

I am trying some of these proposed solutions now.  The datatype for these fields is datetime.  So far, this one from appari seems to be working pretty good:

Select * from yourtableName
where convert(varchar,getdate(),112)
between convert(varchar,startdate,112) and  convert(varchar,enddate,112)

What I want to be sure of that the following logic is implemented in this query:

IF StartDate is less than or equal to the current date AND if EndDate is greater than or equal to the current date, then return the record in the result set.

Does the above query do this?
appari

>>IF StartDate is less than or equal to the current date AND if EndDate is greater than or equal to the current date, then return the record in the result set.

yes this is included in my query from my second post. even cxr's solution will work. the difference between cxr's and my solution is i am converting all the datetimes to yyyymmDD format to ignore time part of the date. if you want to consider time part too in your query filter no need to use the convert functions.
ASKER
dstjohnjr

This query works great and is the one that ended up yielding the best results for my needs.  Thanks!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.