SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Thread: Query dates

  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2006
    Location
    Dubrovnik, Croatia
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query dates

    Hi to all,

    This is example of my table, part of it, so you can see what's the "issue"

    CREATE TABLE `dates` (
    `dates_id` INTEGER(150) NOT NULL AUTO_INCREMENT,
    `date_start` DATE DEFAULT NULL,
    `date_end` DATE DEFAULT NULL,
    `active` TINYINT(4) DEFAULT NULL,
    `name` VARCHAR(70) COLLATE utf8_general_ci DEFAULT NULL,
    PRIMARY KEY (`dates_id`)
    )

    Think of it as article table, i have information about articles (names, titles, authors etc.) and also I have publish dates and end dates (date_start, date_end) and one extra field 'active'. Basically if date (now()) is between two dates and active get me all records. Now i have this query :

    SELECT * FROM dates WHERE
    (NOW() >= date_start OR date_start IS NULL) AND (NOW() < date_end OR date_end IS NULL)
    AND active = 1

    Is there any other way to do this, thank you

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by djomla View Post
    Is there any other way to do this, thank you
    what's wrong with doing it that way?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Dec 2006
    Location
    Dubrovnik, Croatia
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just asking if there is some other better way to do this. this works great but in my head it looks complicated

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    well, there ~is~ another way to do it, but i'm not sure i like it any better

    rather than NULL for begin_date, use an "early" value, like 1970-01-01

    rather than NULL for end_date, use a "late" value, like 2099-12-31

    then your WHERE clause simplifies to:
    Code:
    WHERE date_start <= CURRENT_DATE 
      AND CURRENT_DATE <= date_end
      AND active = 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •