SitePoint Sponsor

User Tag List

Results 1 to 8 of 8

Hybrid View

  1. #1
    SitePoint Wizard Dean C's Avatar
    Join Date
    Mar 2003
    Location
    England, UK
    Posts
    2,906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Converting to an integer

    Code:
    	SELECT 
    	        SUBSTRING_INDEX(birthday_search, '-', 1) AS bday_year
    	FROM 
    	        user
    	WHERE 
    	       birthday_search <> '0000-00-00'
    What I want to do is select all users who are older than 18 from my user table. Ignoring the terrible database design of storing birthdays in the format YYYY-MM-DD which is not my doing, I have managed to get the year from this string. But as it's not a number when I try and do calculations on it, it screws up.

    Any ideas how to get bday_year as an integer. Then I can just take it away from the current year to find out the age

  2. #2
    SitePoint Wizard Lats's Avatar
    Join Date
    Jun 2003
    Location
    Melbourne, AU
    Posts
    1,142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try using the natural date functions...
    Code:
    SELECT year( birthday_search )
    http://dev.mysql.com/doc/mysql/en/Da...functions.html
    Lats...

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    when you say storing YYYY-MM-DD is terrible design, yes, it's bad, assuming what you mean is that it's a VARCHAR datatype

    alter the table and change it to DATE datatype and then you're in business

    but if for some reason you cannot change the datatype, you may have to CAST it explicitly or do an implicit conversion in your query

    e.g.

    select year(cast birthday_search as date) as yyyy

    select cast(left(birthday_search,4) as integer) as yyyy

    select 0+left(birthday_search,4) as yyyy
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Wizard Lats's Avatar
    Join Date
    Jun 2003
    Location
    Melbourne, AU
    Posts
    1,142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    when you say storing YYYY-MM-DD is terrible design, yes, it's bad, assuming what you mean is that it's a VARCHAR datatype
    It's a date field - from the user table of vBulletin.
    Lats...

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    i'm sorry, i was misled by the comment "the terrible database design of storing birthdays in the format YYYY-MM-DD which is not my doing"

    i assumed the problem was treating this field as a DATE field when it's not

    but since it actually is a DATE field (which, by the way, is actually the best database design for dates), then why not just utilize an age calculation that is correct right down to the current date?

    Code:
    select user_name
         , birthday_search
         , year(current_date) 
         - year(birthday_search)
         - case when month(current_date) 
                   > month(birthday_search) 
                then 0
                when month(current_date) 
                   < month(birthday_search) 
                then 1
                when day(current_date) 
                   < day(birthday_search) 
                then 1 
                else 0
            end   AS age
      from yourtable
     where year(current_date) 
         - year(birthday_search)
         - case when month(current_date) 
                   > month(birthday_search) 
                then 0
                when month(current_date) 
                   < month(birthday_search) 
                then 1
                when day(current_date) 
                   < day(birthday_search) 
                then 1 
                else 0
            end               >= 18
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard Dean C's Avatar
    Join Date
    Mar 2003
    Location
    England, UK
    Posts
    2,906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for all your help guys, in the end I could only get this one working:

    Code:
    SELECT 
            (year(current_date) - year( birthday_search )) AS age, 
            username
    FROM 
            user
    WHERE
            birthday_search <> '0000-00-00'
    I unfortunately couldn't get rudy's to work. I changed user_name to username and yourtable to user:

    Code:
    select username
         , birthday_search
         , year(current_date) 
         - year(birthday_search)
         - case when month(current_date) 
                   > month(birthday_search) 
                then 0
                when month(current_date) 
                   < month(birthday_search) 
                then 1
                when day(current_date) 
                   < day(birthday_search) 
                then 1 
                else 0
            end   AS age
      from user
     where year(current_date) 
         - year(birthday_search)
         - case when month(current_date) 
                   > month(birthday_search) 
                then 0
                when month(current_date) 
                   < month(birthday_search) 
                then 1
                when day(current_date) 
                   < day(birthday_search) 
                then 1 
                else 0
            end               >= 18
    But I got this SQL error:

    Code:
    #1064 - You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near '(current_date)
                   < day(birthday_search)

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    oops, my bad
    Code:
    select username
         , birthday_search
         , year(current_date) 
         - year(birthday_search)
         - case when month(current_date) 
                   > month(birthday_search) 
                then 0
                when month(current_date) 
                   < month(birthday_search) 
                then 1
                when dayofmonth(current_date) 
                   < dayofmonth(birthday_search) 
                then 1 
                else 0
            end   AS age
      from user
     where year(current_date) 
         - year(birthday_search)
         - case when month(current_date) 
                   > month(birthday_search) 
                then 0
                when month(current_date) 
                   < month(birthday_search) 
                then 1
                when dayofmonth(current_date) 
                   < dayofmonth(birthday_search) 
                then 1 
                else 0
            end               >= 18
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard Dean C's Avatar
    Join Date
    Mar 2003
    Location
    England, UK
    Posts
    2,906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It won't let me give you more rep :P! Thanks a million rudy!


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
  •