sql syntax to compare date in range

I am comparing a date with two entries made my the user. User wants to display all results for one single day, say today. So they enter 11/06/2014 for both start and end dates (MMColParam3 and MMColParam4)

I would think that the where clause below would display the results, but it doesn't. I need to enter 11/06/2014 to 11/07/2014 to get results where b.apymntdate is 110/06/2014

How can I fix it so that if user enters the same date in both start and end date and if b.apymntdate is the same date it WILL display the results.

WHERE b.apymntdate >= 'MMColParam3' and b.apymntdate <= 'MMColParam4'
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Paul MacDonaldDirector, Information SystemsCommented:
You could try...
     WHERE b.apymntdate >= DATEADD (day, -1, 'MMColParam3') AND b.apymntdate <= 'MMColParam4'
...but other queries may get funny.

What you really should do is CAST the start and end dates to DATETIME.  Then force the start date to have a time of 00:00:01 (midnight) and force the end date to have a time of 23:59:59.   Then your same-day queries would work okay.
AleksAuthor Commented:
How can I do that ?

This is the full query.


SELECT a.paymntid, b.mainpaymntid, a.Itemdesc, b.apymntdate, a.Pmtrecd, c.id, c.caseid, d.firstnm, d.lastnm, f.firmname, f.id
FROM dbo.BillPaymntsRecvd a INNER JOIN BillsMainPaymnts as b ON b.MainPaymntId = a.BillsMainId INNER JOIN CASES as c on a.caseid = c.id left join users  as d on c.alienid = d.userid  Left join FirmAddresses as f on c.FirmAddressId = f.id
WHERE b.paymntfirmid = MMColParam and f.id IN (MMColParam2) and b.apymntdate >= 'MMColParam3' and b.apymntdate <= 'MMColParam4'
Vitor MontalvãoMSSQL Senior EngineerCommented:
What are b.apymntdate, 'MMColParam3' and 'MMColParam4'?
5 Ways Acronis Skyrockets Your Data Protection

Risks to data security are risks to business continuity. Businesses need to know what these risks look like – and where they can turn for help.
Check our newest E-Book and learn how you can differentiate your data protection business with advanced cloud solutions Acronis delivers

Scott PletcherSenior DBACommented:
Code below strips the time, if any, from the MMColParam#.  For the end date, one day is added so that the comparison becomes < rather than <=.

WHERE b.paymntfirmid = MMColParam and
      f.id IN (MMColParam2) and
      b.apymntdate >= dateadd(day, datediff(day, 0, 'MMColParam3'), 0) and b.apymntdate < dateadd(day, datediff(day, 0, 'MMColParam4') + 1, 0)  -- < NOT <=

Btw, to be safe, you should send the date in as '20141106' rather than '11/06/2014'.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AleksAuthor Commented:
b.apymntdate is a datetime

MMColParam3 is a 'start date' entered by user like =  11/06/2014
MMColParam4 is an 'end date' entered by user like =  11/06/2014

if b.apymntdate is 11/06/2014, then it should appear on the results despite of the time, but it doesn't with the above query.
AleksAuthor Commented:
why are you adding one ?  what If there is a record with 11/07/2014 date ?  it would show in the results right ?

  b.apymntdate >= dateadd(day, datediff(day, 0, 'MMColParam3'), 0) and b.apymntdate < dateadd(day, datediff(day, 0, 'MMColParam4') + 1, 0)
ste5anSenior DeveloperCommented:
As Paul already pointed out, it's a DATETIME issue.

I guess you're storing your values in a DATETIME column. So the first question is why? Why do you store apymntdate as DATETIME and not as DATE as the column name indicates?

btw, using SQL Serve 2012+ would result in a predicate pushdown, which allows to us WHERE CAST(T.APymntDate AS DATE) = '20140701'; as conditinon which uses an index.
Scott PletcherSenior DBACommented:
>> why are you adding one ?  what If there is a record with 11/07/2014 date ?  it would show in the results right ? <<

No, it would not.  I'm adding one because I'm using less than, not less than or equal to.

Let's check it out:
start date = 11/06/2014 9:43AM
end date = 11/06/2014 11:14PM

My query does this:
1) strip the time from start date, thus:
start date = 11/06/2014 00:00:00.000
2) strip the time from end date, thus:
end date = 11/06/2014 00:00:00.000
3) add one day to end date, thus:
end date = 11/07/2014 00:00:00.000

Finally, select all rows where
[datetime_in_table] >= start_date and
[datetime_in_table] < end_date
which will return all rows for any time of day for 11/06 but no other rows.

Even if the data type of the column is changed to datetime2, or to date, the query works correctly since it is using < the next day.  This is a very accurate and useful technique when dealing with dates/datetimes.

for both start and end dates for both start and end dates
Jim HornSQL Server Data DudeCommented:
Looks like the above experts are giving you correct answers.

In case it helps, SQL expert PortletPaul addresses this in his article Beware of Between.  An excellent read, and if it helped you click on the green 'Was this article helped you?' button at the end.
AleksAuthor Commented:
Thanks. Ill try the above query and see how that works out.
I am using date time because user also requires the date, just not for this specific report.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.