List:General Discussion« Previous MessageNext Message »
From:ESV Media GmbH Date:September 6 2006 3:05pm
Subject:Getting next Birthdays
View as plain text  
Hello MySQL-User,

i need your help with the following query.
I want to get the next birthdays of my friends, but not only the one of 
this year.
So when im in december ( or november ) i want to see the birthdays of 
next year too....
With my query i only see the birthdays of this year :

SELECT SQL_CACHE DISTINCT fname,lname, mem.mem_id AS p_id, 
DATE_FORMAT(mem.birthday,'%d.%m') AS geburtstag,
DATEDIFF(DATE_FORMAT(mem.birthday,CONCAT(DATE_FORMAT(NOW(),'%Y'),'-%m-%d')),DATE_FORMAT(NOW(),'%Y-%m-%d'))

as tage
FROM members mem
INNER JOIN network n ON ( ( n.mid = 1 AND n.action = 1 AND 
n.bid=mem.mem_id ) OR ( n.bid = 1 AND n.action = 1 AND n.mid=mem.mem_id) )
WHERE DATE_SUB(DATE_FORMAT(NOW(),'%Y-%m-%d'),INTERVAL 1 DAY) <= 
DATE_FORMAT(mem.birthday,CONCAT(DATE_FORMAT(NOW(),'%Y'),'-%m-%d'))
ORDER BY tage

Thx a lot !!!

Cheers

Marco

spacemarc schrieb:
> 2006/9/6, Miles Thompson <miles@stripped>:
>> First of all - please reply to the list ..
>>
>> I thought you wanted all fields, that's the way your SELECT statement is
>> constructed. If just the first 20 char from fieldA, then ...
>>          SELECT LEFT(fieldA, 20) AS fieldA FROM Tab1
>> should do what you want. Again, check the syntax for LEFT() in the 
>> MySQL docs.
>
> the syntax is valid:
> from http://dev.mysql.com/doc/refman/5.0/en/string-functions.html:
> "LEFT(str,len) returns the leftmost len characters from the string
> str, or NULL if any argument is NULL."
> mysql> SELECT LEFT('foobarbar', 5);
>        -> 'fooba'
>
> I have 30 fields: instead writing all 30 fields, I would want to
> select them all with * and only for one of they, fieldA, to obtain the
> first 20 chars: it's possible?
>
>


Thread
Getting next BirthdaysESV Media GmbH6 Sep
  • RE: Getting next BirthdaysDaevid Vincent13 Sep