Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2006
    Posts
    10

    Unanswered: Need Help with DateTime Query

    Hey gang, I need a little help creating a Query. I have a table storing info about visitors to our company. The name of the table is "visitors" and it has an "arrivalDate" field and a "departureDate" field. Both of these fields are of DateTime data type. I need to run a report each day to see which visitors are currently on site. For a partial example, I will use today's date of 1/16/08:

    Code:
    SELECT name FROM visitors WHERE ((MONTH(arrivalDate) = 1 AND DAY(arrivalDate) >= 15 AND YEAR(arrivalDate) = 2008) AND 
    (MONTH(departureDate) ?????
    It is the second part of the query that I am having trouble with. Am I going about this all wrong? Is there a way to extract this information when all I know is the persons arrival date, departure date, and the current days date? Any help would be greatly appreciated.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If the person has not left yet, then surely departureDate IS NULL?
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You'll need to tweak this depending on how you are handling datetime values:
    Code:
    SELECT	name
    FROM	visitors
    where	ArrivalDate <= getdate()
    		and (DepartureDate > getdate() or DepartureDate is null)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Oct 2006
    Posts
    10
    I guess I forgot to include the fact that the arrival date and the departure date is populated prior to the visitors arrival There is a real date in every field neither the departureDate nor the arrivalDate field will except a null value. For example:

    name: John Doe
    arrivalDate: 1/16/08
    departureDate: 1/20/08

    So every morning between 1/16/08 and 1/20/08 John Doe must show up on the report

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT *
    FROM   myTable
    WHERE  DateAdd(d, DateDiff(d, 0, GetDate()), 0) >= arrivalDate
    AND    DateAdd(d, DateDiff(d, 0, GetDate()), 0) < departureTime
    EDIT: Scratch this query... Blindmans will do what you want.
    George
    Home | Blog

  6. #6
    Join Date
    Oct 2006
    Posts
    10
    many thanks georgev, thats seems to work perfect. cheers!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •