Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    Jul 2002
    Location
    The Netherlands
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to handle dates when year isn't always known ..

    Hi,

    This post is partially related to storing something in a database, but also concerns PHP, so that's the reason I'm posting it in this forum.

    I'm currently building a website which contains a birthday calendar and I'm wondering what the best way is to store the dates of the birthdays. The first answer that came to my mind is to use MySQL's date-field. The only thing is that in some cases the birthyear is left empty (simply because somebody that adds a person to his calendar, doesn't know the year of birth ..)

    A second option I was thinking about is to use 3 columns (day, month, year).

    The only problem is that I easily want to be able to list the upcoming birthdays for the next 7 days, even if it exceeds a month. Then this approach isn't that practical, I guess.

    I'd appreciate it if somebody can share his advice on this!

    Cheers!- Michiel

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Store the date as a "date" data type in MySQL. This will give you all the flexibility you need-- you can format the date any way you like, you can perform comparisons on the date, you can perform addition and subtraction on the date, and yes, you can find all the birthdays coming up in the next 7 days.

    The reason you gave for not using a MySQL date column is resolved by making the column NULLable and then simply insert a null into the column if the birthday is not known.

  • #3
    Regular Coder
    Join Date
    Jul 2002
    Location
    The Netherlands
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ok .. I'm using the date data type now .. but haven't figured out how to select the upcoming birthdays, in a way that I can go beyond the months. So when you're close to the end of a month, I'd want to select the birthdays of the first days of the next month as well.

    I'm currently using DAYOFMONTH(birthday) and MONTH(birthday) to do the selecting. Off course I can come up with a query, using these functions and still span over months, but it seems like there should be a simpler solution ..

    Thanks so far! -Michiel

  • #4

  • #5
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    There is a solution, but it's kind of tricky because you have to ignore the "year" part of the birthday. Normal date comparison, such as finding all account that expire in the next month, is easy to accomplish with the date_add() MySQL function.

    Anyways here is an example of how I did it (there may be a simpler way):

    Code:
    //The WHERE clause assembles each birthday using the current year
    //(or next year if the birthday has already passed for the year),
    //then qualifies that date if it falls between current date and 1 week from now.
    
    SELECT first_nm,
    FROM people_tbl
    WHERE if (
    concat(year(now()),'-',date_format(birth_dt, '%%m'), '-', date_format(birth_dt, '%%d')) >= curdate(),
    
    concat(year(now()), '-', date_format(birth_dt, '%%m'), '-', date_format(birth_dt, '%%d')),
    
    concat(year(now()  + interval 1 year), '-', date_format(birth_dt, '%%m'), '-', date_format(birth_dt, '%%d')))
    
    between date(now()) and date((now()) + interval 1 week)


  •  

    Posting Permissions

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