Solved

SQL Query to find date between two datetime fields

Posted on 2013-12-09
6
515 Views
Last Modified: 2013-12-09
I have a webform with a gridview. The gridview loads based on a date that equals the visitDate field or the endVisitDate field or a date between the two.

I have the part that selects the date equal to the visitDate or equal to the endVisitDate but I'm getting stuck on how to pull the date from between the two.

This is the query that works:
"SELECT visitorKey, visitorName1, visitorName2, visitDate, visitEndDate
FROM dbo.VisitorLog WHERE (DATEDIFF(d, visitDate, GETDATE()) = 0) OR (DATEDIFF(d, visitEndDate, GETDATE()) = 0) "

How do I do the third part selecting between the two dates?
Any help would be appreciated
0
Comment
Question by:lakhi
6 Comments
 
LVL 25

Expert Comment

by:Shaun Kline
Comment Utility
What you are asking is slightly confusing. You have a web form with two fields that will load a gridview. So you want to filter the data in your VisitorLog table based on these fields. So you can easily do this by using the BETWEEN keyword:

WHERE visitDate BETWEEN <visit start date field> AND <visit end date field>

If you need the end date to be inclusive, use the DATEADD field to add 1 day to it (or handle in code prior to passing it to your query).
0
 

Author Comment

by:lakhi
Comment Utility
I am very sorry. I left part of my issue out. The gridview loads only the names of the visitors who will be in today. So the visitDate could be today, the visitEndDate could be today or today could be part of the range of dates between the visitDate and the visitEndDate.
People can submit requests for a visit on one day only or submit an advance request for a visit of several days.

So the problem, as I see it, is to find "today" between the visitDate and the visitEndDate.

Could I still use "between" to cover all of three possibilities?
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
Comment Utility
In general it's better to avoid "between" for dates.  The below should work for you:

      getdate() < visitendDate and
      getdate() >= visitDate
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
SQL expert PortletPaul wrote an entire article on Beware of Between that would be a good read.
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 300 total points
Comment Utility
SELECT visitorKey, visitorName1, visitorName2, visitDate, visitEndDate
FROM dbo.VisitorLog
WHERE
    GETDATE() >= visitDate AND
    DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) <= visitEndDate
0
 

Author Closing Comment

by:lakhi
Comment Utility
The solution worked perfectly. It was just what I needed to know. Thanks!
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Modifying SQL 2008/2012 PARTITIONS 3 46
Parsing the XML data to SQL Server 4 50
Need more details 5 81
Problem with SqlConnection 5 109
What my article will show is if you ever had to do processing to a listbox without being able to just select all the items in it. My software Visual Studio 2008 crystal report v11 My issue was I wanted to add crystal report to a form and show…
For a while now I'v been searching for a circular progress control, much like the one you get when first starting your Silverlight application. I found a couple that were written in WPF and there were a few written in Silverlight, but all appeared o…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

771 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

15 Experts available now in Live!

Get 1:1 Help Now