Solved

sql syntax to compare date in range

Posted on 2014-11-06
10
232 Views
Last Modified: 2014-11-06
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'
0
Comment
Question by:amucinobluedot
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 34

Assisted Solution

by:Paul MacDonald
Paul MacDonald earned 125 total points
ID: 40426413
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.
0
 

Author Comment

by:amucinobluedot
ID: 40426428
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'
---
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40426434
What are b.apymntdate, 'MMColParam3' and 'MMColParam4'?
0
MongoDB Through a MySQL Lens

This article looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.

 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 250 total points
ID: 40426472
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'.
0
 

Author Comment

by:amucinobluedot
ID: 40426481
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.
0
 

Author Comment

by:amucinobluedot
ID: 40426508
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)
0
 
LVL 34

Assisted Solution

by:ste5an
ste5an earned 125 total points
ID: 40426549
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.
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 250 total points
ID: 40426615
>> 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
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40426702
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.
0
 

Author Comment

by:amucinobluedot
ID: 40426980
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.
0

Featured Post

Percona Monitoring and Management and Grafana

Proactive monitoring is vital to a highly-available environment. We have a quick start guide on Experts Exchange for Grafana users.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question