Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

sql syntax to compare date in range

Posted on 2014-11-06
10
Medium Priority
?
236 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:Aleks
10 Comments
 
LVL 34

Assisted Solution

by:Paul MacDonald
Paul MacDonald earned 500 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:Aleks
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 52

Expert Comment

by:Vitor Montalvão
ID: 40426434
What are b.apymntdate, 'MMColParam3' and 'MMColParam4'?
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 1000 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:Aleks
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:Aleks
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 36

Assisted Solution

by:ste5an
ste5an earned 500 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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1000 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:Aleks
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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.​
Integration Management Part 2
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

824 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