Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2010
    Posts
    7

    Post Unanswered: DATETIME error...pls help

    there is a query in the database saying:
    Find all flights that depart TODAY and TOMORROW from Tokyo airport?

    and I did write this query:

    SELECT *
    FROM tblFlights F
    WHERE F.DepartureAirport ='TKY' AND F.DepartureDateTime='1/5/2010'

    UNION

    SELECT *
    FROM tblFlights F2
    WHERE F2.DepartureAirport ='TKY' AND F2.DepartureDateTime='2/5/2010'


    logically it is OK i guess, but the datatype (domain) at the columns DepartureDateTime is deined as DATETIME and it contains data both for the DATE and the TIME and all I need is just DATE,


    the problem is how do I make this query to check just for the DATE because it returns errors, plus deleting the time from this columns will not be the solution that will help me, I don't know if there is any trick like with VARCHARS when we can use "_%" or similar things to define what we want to check for....
    Edit/Delete Message

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by siemprepeligroso View Post
    Find all flights that depart TODAY and TOMORROW from Tokyo airport?
    Code:
    WHERE F.DepartureAirport ='TKY' 
      AND F.DepartureDateTime >= DATEADD(day,DATEDIFF(day,0,GETDATE()),0)
      AND F.DepartureDateTime  < DATEADD(day,DATEDIFF(day,0,GETDATE())+2,0)
    the first expression returns midnight of the current date, i.e. the start of today, and notice the condition is "greater than or equal"

    the second expression adds 2 days, which gives the start of the day after tomorrow, but notice that the condition is "less than"

    these endpoints bracket a range of datetimes which correspond to today and tomorrow
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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