Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Query to find date between two datetime fields

Posted on 2013-12-09
6
Medium Priority
?
525 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
[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
6 Comments
 
LVL 27

Expert Comment

by:Shaun Kline
ID: 39706679
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
ID: 39706743
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 40

Expert Comment

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

      getdate() < visitendDate and
      getdate() >= visitDate
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 66

Expert Comment

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

Accepted Solution

by:
Scott Pletcher earned 1200 total points
ID: 39707004
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
ID: 39707171
The solution worked perfectly. It was just what I needed to know. Thanks!
0

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
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…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

670 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