• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 238
  • Last Modified:

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'
4 Solutions
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'?
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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'.
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 HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now