apply an if condition in a sql query

Question

i want to use a if condition in a sql query according to following need.
"if a year field is null then do not calculate age and if it is set then it have to execute.
here is my query.where is the problem?please consider this scenario 'if month and date is there like for example 0000-03-12'

SELECT id, name, birth_date, birth_time, 
            city_native, country_native, sex, 
            city_current, country_current, image,
            if(YEAR(birth_date)='','',YEAR(CURDATE()) - YEAR(birth_date) - 
              (RIGHT(CURDATE(),5) < RIGHT(birth_date,5)),'') AS age
        FROM birth_user u
        WHERE <condition>;

Which DBMS are you using?

user330315
– user330315

2014-04-04 11:29:32 +00:00
Commented Apr 4, 2014 at 11:29 — user330315
– user330315, Commented Apr 4, 2014 at 11:29

Mudassir Hasan · Accepted Answer · 2014-04-04 11:18:19Z

1

You can use IFNULL() to check birth_date is NULL

CASE WHEN IFNULL(birth_date,0)=0 THEN '' ELSE YEAR(CURDATE()) - YEAR(birth_date) END as age

answered Apr 4, 2014 at 11:18

Mudassir Hasan

29k21 gold badges104 silver badges135 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Niet the Dark Absol · Accepted Answer · 2014-04-04 11:17:33Z

0

If your need is "if it is null", why are you comparing it to zero?

if(birth_date is null, 0, YEAR(CURDATE()) - ......) AS age

Where that 0 there is a suitable "default age".

answered Apr 4, 2014 at 11:17

Niet the Dark Absol

326k86 gold badges480 silver badges604 bronze badges

5 Comments

Aksh Over a year ago

because in my code if year is not available then set it to '0000' instead of 1970.

Niet the Dark Absol Over a year ago

So it's not null, it's 0000-00-00? In that case, why not just check if( birth_date = '0000-00-00', ...)?

Aksh Over a year ago

but it will fail when there is month and date is there like for example 0000-03-12

Niet the Dark Absol Over a year ago

Why would there ever be a month and date when the year is zero?

Aksh Over a year ago

this is coming from facebook friend list friend,when a user does not allow to show a year feild.

Madhivanan · Accepted Answer · 2014-04-04 11:17:46Z

0

Use NULL instead of 0000

SELECT id, name, birth_date, birth_time, 
city_native, country_native, sex, 
city_current, country_current, image,
if(birth_date is null,'',YEAR(CURDATE()) - YEAR(birth_date) - 
(RIGHT(CURDATE(),5) < RIGHT(birth_date,5)),'') AS age
FROM birth_user u
WHERE " . implode(' AND ', $where);

answered Apr 4, 2014 at 11:17

Madhivanan

13.7k1 gold badge28 silver badges30 bronze badges

1 Comment

Aksh Over a year ago

in my code if year is not available then set it to '0000' instead of 1970.

Collectives™ on Stack Overflow

apply an if condition in a sql query

3 Answers 3

Comments

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related