CODEWITHSUNDEEP
mysql
Param Veer
Param Veer

Reputation: 786

get date from varchar stored date format for friends birthday

I want to get the date from varchar stored date (eg 12-21-1990). I can use php explode to get the current date, but I wanted to get it through mysql. I am using a query like this: 

$count=mysql_query("SELECT u.fname, u.lname, u.profile_pic, u.uid, u.bday, f.uid, f.friend_id, f.status FROM friend_list f, users_profile u WHERE f.uid = '$id' AND f.status = '1' AND u.uid = f.friend_id ");

The above query gets me users friends with all details, but I wanted to only get friends deatails whose birthday is today. How can I do this?

Upvotes: 0

Views: 520

Answers (6)

hemu
hemu

Reputation: 3261

SELECT u.fname, u.lname, u.profile_pic, u.uid, u.bday, f.uid, f.friend_id, f.status FROM 
friend_list f, users_profile u WHERE f.uid = '$id' AND f.status = '1' AND u.uid = f.friend_id 
and CAST(CONCAT(SUBSTRING(u.bday,7,11),'/',SUBSTRING( u.bday,1,2 ),'/',SUBSTRING(u.bday,4,2 )) AS DATE) = date_format(now(),'%Y-%m-%d');

Upvotes: 0

Param Veer
Param Veer

Reputation: 786

hello just found the solution and wanted to share it so that it could help someone .

SELECT u.fname, u.lname, u.profile_pic, u.uid, u.bday, f.uid, f.friend_id, f.status FROM 
    friend_list f, users_profile u WHERE f.uid = '3' AND f.status = '1' AND u.uid = f.friend_id 
      and    DAY(STR_TO_DATE(u.bday, '%m-%d-%y')) = DAY(CURDATE()) AND
    MONTH(STR_TO_DATE(u.bday, '%m-%d-%y')) = MONTH(CURDATE())

Upvotes: 0

JDGuide
JDGuide

Reputation: 6525

Try this :

date_format(your_friend_birthdate,'%Y-%m-%d')=date_format(now(),'%Y-%m-%d')

Full Query :-

    SELECT u.fname, u.lname, u.profile_pic, u.uid, u.bday, f.uid, f.friend_id, f.status FROM 
friend_list f, users_profile u WHERE f.uid = '$id' AND f.status = '1' AND u.uid = f.friend_id 
and month(u.bday)=month(now()) and date(u.bday)=date(now());

or

SELECT u.fname, u.lname, u.profile_pic, u.uid, u.bday, f.uid, f.friend_id, f.status FROM 
    friend_list f, users_profile u WHERE f.uid = '$id' AND f.status = '1' AND u.uid = f.friend_id 
    and month(u.bday)=month(now()) and day(u.bday)=day(now());

Upvotes: 0

FedeX
FedeX

Reputation: 446

Easy you just need to use the STR_TO_DATE MySQL function to convert your varchar to date:

$count=mysql_query("SELECT u.fname, u.lname, u.profile_pic, u.uid, u.bday, f.uid, f.friend_id, f.status FROM friend_list f, users_profile u WHERE f.uid = '$id' AND f.status = '1' AND u.uid = f.friend_id AND STR_TO_DATE(u.bday, '%m-%d-%Y')=DATE_FORMAT(CURDATE().'%m-%d-%Y') ");

But I strongly recommend you change the bday column to date type.

Upvotes: 0

jane
jane

Reputation: 161

I would change the bday column data type to DATE and use CUREDATE() function

Upvotes: 0

MimiEAM
MimiEAM

Reputation: 2620

you can get today's date with php date function , assign it to a variable and check the u.bday against it. like so :

$count=$today = date("m-d-Y");

mysql_query("SELECT u.fname, u.lname, u.profile_pic, u.uid, u.bday, f.uid, f.friend_id, f.status FROM friend_list f, users_profile u WHERE f.uid = '$id' AND f.status = '1' AND u.uid = f.friend_id AND u.bday = $today");

Upvotes: 2

Related Questions