SitePoint Sponsor |
|
User Tag List
Results 1 to 8 of 8
Thread: Converting to an integer
-
Dec 28, 2004, 14:47 #1
- 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'
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
-
Dec 28, 2004, 16:32 #2
- 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 )
Lats...
-
Dec 28, 2004, 17:00 #3
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 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
-
Dec 28, 2004, 17:15 #4
- Join Date
- Jun 2003
- Location
- Melbourne, AU
- Posts
- 1,142
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally Posted by r937
Lats...
-
Dec 28, 2004, 17:32 #5
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 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
-
Dec 30, 2004, 07:57 #6
- 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'
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
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)
-
Dec 30, 2004, 08:46 #7
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 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
-
Dec 30, 2004, 09:27 #8
- 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