Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003
    Posts
    6

    Unanswered: search for a date close to a given one

    search for a date close to a given one
    hello!

    i have a little problem (in fact quite big for me..)

    i have a database of notes and each note has a creation date( date_creation);

    when i am seeing a note i have to links, one to the following note and one to the note before this one;

    i will give you an exemple :

    in my database i have the following entries:

    id_note date_creation
    1 2003-08-01
    2 2003-08-02
    3 2003-08-06
    4 2003-08-03
    5 2003-08-10
    6 2003-08-08

    i am watching the note wrote in 2003-08-06;
    i want to have a link to 2003-08-03 and one to 2003-08-08

    how can i search for the 2 dates in the neighbourhood of the given date

    i have tried to order the database but i don(t know how to extract the dates

    thanks a lot

  2. #2
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1

    Re: search for a date close to a given one

    Originally posted by brux
    search for a date close to a given one
    hello!

    i have a little problem (in fact quite big for me..)

    i have a database of notes and each note has a creation date( date_creation);

    when i am seeing a note i have to links, one to the following note and one to the note before this one;

    i will give you an exemple :

    in my database i have the following entries:

    id_note date_creation
    1 2003-08-01
    2 2003-08-02
    3 2003-08-06
    4 2003-08-03
    5 2003-08-10
    6 2003-08-08

    i am watching the note wrote in 2003-08-06;
    i want to have a link to 2003-08-03 and one to 2003-08-08

    how can i search for the 2 dates in the neighbourhood of the given date

    i have tried to order the database but i don(t know how to extract the dates

    thanks a lot
    Something like:

    (select id_note, date_creation
    from test_date
    where date_creation < '2003-08-06'
    limit 1)

    union

    (select id_note, date_creation
    from test_date
    where date_creation > '2003-08-06'
    limit 1);

    Should do the trick...

  3. #3
    Join Date
    Aug 2003
    Posts
    6

    answer from docktor blue

    hi,

    thanks for the answer, doctor blue has already gave me another one that works perfectly....

    anyway 10x a lot

    **************************
    Sounds difficult, but it isn't. You can query you note table for the neighbours of a particular date, call it d:

    The previous date:
    SELECT max(Date_Creation) FROM Notes WHERE Date_Creation < d

    The next date:
    SELECT min(Date_Creation) FROM Notes WHERE Date_Creation > d


    If your d is the first or the last date, you just get NULL from your corresponding queries.

    If you have several notes on the same day, you will have to refine the approach.

Posting Permissions

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