Solved

sql syntax to compare date in range

Posted on 2014-11-06
10
224 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
10 Comments
 
LVL 33

Assisted Solution

by:paulmacd
paulmacd 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 45

Expert Comment

by:Vitor Montalvão
ID: 40426434
What are b.apymntdate, 'MMColParam3' and 'MMColParam4'?
0
 
LVL 69

Accepted Solution

by:
ScottPletcher 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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 32

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:ScottPletcher
ScottPletcher 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 65

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

743 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now