Results 1 to 2 of 2

Thread: Query datetime datatype with date only

  1. #1
    Join Date
    Mar 2006
    Posts
    41
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Default Query datetime datatype with date only

    I'm using the following query:
    SELECT * FROM IT_event_cal WHERE '$date' BETWEEN start_date AND stop_date
    Where start and stop_date are of type date/time (0000-00-00 00:00:00).

    For this particular query, I'd like to match a particular date (0000-00-00), and any time. How can I make the time part a wild-card value?

    I use these columns in other parts of the application where I need the time. While I could break the times out into new columns, it seems more "tidy" to use the datetime data type.

    Example:

    Assume a record following values exists:

    start_date: 2008-07-10 01:00:00
    stop_date: 2008-07-10 03:00:00

    then,

    SELECT * FROM IT_event_cal WHERE '2008-07-10' BETWEEN start_date AND stop_date

    returns an empty set. In other words no values. I want a result set that includes every record where the date part is (in this example) '2008-07-10'

  2. #2
    Join Date
    Jul 2008
    Location
    Johannesburg, South Africa
    Posts
    31
    Thanks
    1
    Thanked 10 Times in 10 Posts

    Default

    Really depends which you find more elegant or not but you can either alter the $date and add a dummy time or just use the DATE() function extract only the date from the timestamp fields...

    Code:
    SELECT * FROM IT_event_cal WHERE '$date' BETWEEN DATE(start_date) AND DATE(stop_date)
    OR

    Code:
    SELECT * FROM IT_event_cal WHERE '$date 12:00:00' BETWEEN start_date AND stop_date

Bookmarks

Posting Permissions

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