Converting to an integer

[Dean C]

	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

[Lats]

Try using the natural date functions...

SELECT year( birthday_search )

http://dev.mysql.com/doc/mysql/en/Da...functions.html

[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

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

[Lats]

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.

[r937]

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?

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

[Dean C]

Thanks for all your help guys, in the end I could only get this one working:

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:

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:

#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)

[r937]

oops, my bad

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

[Dean C]

It won't let me give you more rep :P! Thanks a million rudy!