Link to home
Start Free TrialLog in
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!
Avatar of appari
appari
Flag of India image

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
Avatar of appari
appari
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Try this:
select * from TableName
where curdate() between StartDate and EndDate

Open in new window

Avatar of nmcdermaid
nmcdermaid

What datatype are StartDate and EndDate? Are they DATETIME or VARCHAR?
Avatar of dstjohnjr

ASKER

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?
>>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.
This query works great and is the one that ended up yielding the best results for my needs.  Thanks!