SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Guru coiL's Avatar
    Join Date
    Sep 2001
    Location
    QLD, Australia
    Posts
    666
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    how to compare dates?

    Hi,

    How would I return a date from the database that is 7 days more then the date specified?

    eg: I want to find all the records that were entered 1 week after the 2nd of febuary.


    I know that
    Code:
    $sql = "SELECT date + INTERVAL 7 DAY AS date2 from tablename";
    will return the date one week from the earliest date in the database - but i want to be able to return the date 1 week from any date specified.

    I hope this is clear.


    Thanks
    Last edited by coiL; Mar 2, 2003 at 23:12.
    coiL
    "cradled in the learning curve"

  2. #2
    if($awake){code();} PHP John's Avatar
    Join Date
    Jul 2002
    Location
    Along the Wasatch Fault line.
    Posts
    1,771
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by coiL
    Hi,

    How would I return a date from the database that is 7 days more then the date specified?

    eg: I want to find all the records that were entered 1 week after the 2nd of febuary.


    I know that
    Code:
    $sql = "SELECT date + INTERVAL 7 DAY AS date2 from tablename";
    will return the date one week from the earliest date in the database - but i want to be able to return the date 1 week from any date specified.

    I hope this is clear.


    Thanks
    What data type are you storing your "date" as? Is it a TIMESTAMP, DATE, or DATE/TIME?

    Maybe: $sql = "SELECT date + INTERVAL 7 DAY AS date2 from tablename WHERE date = 'February 2'";

    and "February 2" would be in the proper format for the comparision.

    (just a shot in the dark for now! )
    John

  3. #3
    SitePoint Guru coiL's Avatar
    Join Date
    Sep 2001
    Location
    QLD, Australia
    Posts
    666
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it's stored as DATE

    Thank you, I should have figured that out myself
    coiL
    "cradled in the learning curve"

  4. #4
    Happy Holidays !! Paul S's Avatar
    Join Date
    Mar 2001
    Location
    Mexico
    Posts
    1,287
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I guess the query should be something like this
    Code:
    SELECT date FROM tablename WHERE date BETWEEN '20030202' AND '20030202' + INTERVAL 7 DAY
    Paul

  5. #5
    SitePoint Guru coiL's Avatar
    Join Date
    Sep 2001
    Location
    QLD, Australia
    Posts
    666
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Paul S
    I guess the query should be something like this
    Code:
    SELECT date FROM tablename WHERE date BETWEEN '20030202' AND '20030202' + INTERVAL 7 DAY
    Paul

    that also might be useful.

    Should it be
    Code:
     WHERE date BETWEEN '20030202' AND ('20030202' + INTERVAL 7 DAY)
    ?
    Last edited by coiL; Mar 3, 2003 at 00:41.
    coiL
    "cradled in the learning curve"

  6. #6
    if($awake){code();} PHP John's Avatar
    Join Date
    Jul 2002
    Location
    Along the Wasatch Fault line.
    Posts
    1,771
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Beware of using "date" as a field name. It is a reserved word in mySQL, and can produce much confusion!
    John

  7. #7
    Happy Holidays !! Paul S's Avatar
    Join Date
    Mar 2001
    Location
    Mexico
    Posts
    1,287
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    nope, the parentheses are not necessary

    Paul

  8. #8
    Happy Holidays !! Paul S's Avatar
    Join Date
    Mar 2001
    Location
    Mexico
    Posts
    1,287
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    no, date is not a reserved word in mysql, but it's a great suggestion using another name (in case you want to migrate to another db server)
    The following symbols (from the table above) are disallowed by ANSI SQL but allowed by MySQL as column/table names. This is because some of these names are very natural names and a lot of people have already used them.

    ACTION
    BIT
    DATE
    ENUM
    NO
    TEXT
    TIME
    TIMESTAMP
    Paul

  9. #9
    SitePoint Guru coiL's Avatar
    Join Date
    Sep 2001
    Location
    QLD, Australia
    Posts
    666
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks for those 2 suggestions, very helpful indeed

    And I don't really use date as a field name, was just to simplify the example.

    thanks again
    coiL
    "cradled in the learning curve"

  10. #10
    if($awake){code();} PHP John's Avatar
    Join Date
    Jul 2002
    Location
    Along the Wasatch Fault line.
    Posts
    1,771
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Paul S
    no, date is not a reserved word in mysql, but it's a great suggestion using another name (in case you want to migrate to another db server)Paul

    Hmmm.... Thanks! I made the assumption because it is a data type, and have seen problems with other words mySQL uses in its language used as field names.

    But, good to know, and good to avoid.
    John

  11. #11
    SitePoint Member
    Join Date
    Oct 2004
    Location
    malaysia
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question how to compare current date with the date in database

    hye..

    i am new to jsp. i want to know how to campare current date or today's date with the date in database. I want to generate a reminder for license expiration date. i would appreciate if somebody could guide me how this can be done. Better still if somebody can send me the sample code with some explanation. please help me...do reply as soon as possible..

    thanks in advance...

  12. #12
    Are You There? KDesigns's Avatar
    Join Date
    Oct 2003
    Location
    Your Monitor
    Posts
    1,146
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by azira
    hye..

    i am new to jsp. i want to know how to campare current date or today's date with the date in database. I want to generate a reminder for license expiration date. i would appreciate if somebody could guide me how this can be done. Better still if somebody can send me the sample code with some explanation. please help me...do reply as soon as possible..

    thanks in advance...
    How is the date stored in the database? Is it just a VARCHAR type or is it an actual DATE type? If it is just a normal field type you can do this:

    PHP Code:
    //Get Current Date
    $current_date date('m-d-y');

    //Query DB for Expiration Date
    $sql "SELECT exp_date FROM table WHERE something = 'something'";
    $query mysql_query($sql);

    $row mysql_fetch_assoc($query);

    $exp_date $row['exp_date'];

    //Compare the two
    if($current_date $exp_date) {
         echo(
    'You're expired!');

    ChooseDaily.com - Follow on Twitter
    Top Resources for Web Designers and Developers Every Day!

  13. #13
    SitePoint Member
    Join Date
    Oct 2004
    Location
    malaysia
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks... KDesigns,
    I store the date like this 'dd-mm-yyyy' format. The code that u suggest for me is in php code but I'm using JSP code and oracle for my database in my project. So, can i apply it in JSP?

  14. #14
    Are You There? KDesigns's Avatar
    Join Date
    Oct 2003
    Location
    Your Monitor
    Posts
    1,146
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by azira
    Thanks... KDesigns,
    I store the date like this 'dd-mm-yyyy' format. The code that u suggest for me is in php code but I'm using JSP code and oracle for my database in my project. So, can i apply it in JSP?
    I don't have a lot of experience working with Oracle and JSP. I'll leave that one up to those experts. Perhaps you should post a thread in the "General Development", "Java and JSP", or the "Databases" category. Notice the keyword in that last sentence or. Please don't post the same question in all three forums.
    ChooseDaily.com - Follow on Twitter
    Top Resources for Web Designers and Developers Every Day!

  15. #15
    SitePoint Member
    Join Date
    Oct 2004
    Location
    malaysia
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok..
    Anyway, thank you for the information given. I'm really apprecite it.

    Thank you.


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
  •