Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2010
    Posts
    14

    Question Unanswered: Formulas in a query

    OK this may seem elementary but I cannot seem to get this.

    I need a field to be generated in a query that comes from subtracting 2 different dates; Example:

    Client DOB - 01/01/1900

    Client's Child DOB - 01/01/2000

    Result - Client was 100 when child was born.

    Is it possible to just display the result of this equation?

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Have a look at the function DATEDIFF which will give you in days the difference between two dates.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Sep 2010
    Posts
    14
    ok so would this go in the select statment?

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Here is an example taken from MySQL documentation:

    SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  5. #5
    Join Date
    Sep 2010
    Posts
    14
    Thanks so much for your help!!

    I ended up using:

    Code:
    FLOOR (DATEDIFF(o.birthdate,c.birthday)/365) AS 'Age of Client at Babys Birth'
    Is this OK? I ask because FLOOR is not something that I am really familiar with.

  6. #6
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    The function FLOOR simply rounds down to the nearest integer value. So for example, FLOOR(2.5) will return 2, FLOOR(-2.5) will return -3. In the above case you are using it correctly as a person who is 5.25 is considered 5 years old. One thing though is that the number of leap years which might cause some problems over time. I would suggest dividing by 365.25 to get something a little more accurate.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Tags for this Thread

Posting Permissions

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