Results 1 to 15 of 15

Thread: current year

  1. #1
    Join Date
    Mar 2011
    Location
    Sydney, Australia
    Posts
    58

    Unanswered: current year

    How do I express the current year in the following calculation? I've readed masses of notes but can't find this answer.

    I have to find horses over 15 years old. horse_born is the column containing the horse's year of birth:

    SELECT horse_id, horse_name
    FROM horse
    WHERE "expression for current year" - horse_born >15

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    After extensive searching I found the following page.
    The year function looked particularly relevant.

  3. #3
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    To add to Mike's suggestion try using curdate() or now() to get today's date or date/time.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by it-iss.com View Post
    ... try using curdate() or now() to get today's date or date/time.
    please, don't use those

    use these instead -- CURRENT_DATE, CURRENT_TIMESTAMP

    they are totally equivalent, ~and~ they will also work in other database systems, thus making your code portable and not dependent on proprietary mysql syntax

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by melgra70 View Post
    WHERE "expression for current year" - horse_born >15
    don't do a calculation involving the column value, the database engine won't be able to optimize the query

    instead, put the column value on one side of the comparison operator, and do the calculation on the other side --

    WHERE horse_born < YEAR(CURRENT_DATE) - INTERVAL 15 YEAR

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I think I preferred it when we provided NZDF solutions to homework questions rather than high efficiency multi-platform solutions. It was also far more fun.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mike_bike_kite View Post
    I think I preferred it when we provided NZDF solutions to homework questions
    i believe that i did

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I obviously wasn't paying enough attention in class then

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    so i guess this was a good nzdf then?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    oh definitely - keep up the good work

  11. #11
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    nzdf - what does this mean? I googled it and found New Zealand Defense Force and New Zealand Drillers Federation - though I don't think either apply here
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    NZDF is a term which was first coined here in these very forums by, ahem, yours truly

    it stands for Non-Zero Deviosity Factor, and refers to the practice of responding to certain requests for homework assistance with an answer that is devious to some extent

    obviously, a student struggling with a homework problem, who makes a game attempt, and shows his work, and discusses what he's tried and what concepts he's having trouble with, is going to receive answers largely devoid of any deviosity

    the idea is that the larger the deviosity, the more likely it is that the student will get into trouble if he hands in the answer he obtained here

    and it's particularly effective if the answer actually happens to work correctly, but in a way so devious that the student doesn't twig onto the ruse but the teacher surely will

    can you see what the NZDF was in this thread?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    LOL - of course as a student once I was more intent on getting the solution working, less about performance and cross platform compatibility. I like it - NZDF!! I will have to use this too!!
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  14. #14
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I wonder whether we should have a monthly award for the best response. The only danger with NZDF is that some future associate might look for your name on the web and come up with the code and just think WTF?

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mike_bike_kite View Post
    I wonder whether we should have a monthly award for the best response.
    pat would win it in a cakewalk every month

    i've never seen anybody come even close to his level of deviosity

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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