[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 528
  • Last Modified:

SQL Query to find date between two datetime fields

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
lakhi
Asked:
lakhi
1 Solution
 
Shaun KlineLead Software EngineerCommented:
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
 
lakhiAuthor Commented:
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
 
Kyle AbrahamsSenior .Net DeveloperCommented:
In general it's better to avoid "between" for dates.  The below should work for you:

      getdate() < visitendDate and
      getdate() >= visitDate
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
SQL expert PortletPaul wrote an entire article on Beware of Between that would be a good read.
0
 
Scott PletcherSenior DBACommented:
SELECT visitorKey, visitorName1, visitorName2, visitDate, visitEndDate
FROM dbo.VisitorLog
WHERE
    GETDATE() >= visitDate AND
    DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) <= visitEndDate
0
 
lakhiAuthor Commented:
The solution worked perfectly. It was just what I needed to know. Thanks!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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