Return null for date_format when input is null in mysql

Question

I'm doing something like this:

SELECT date_format(mydate, '%d/%m/%Y') FROM xyz;

When mydate is NULL, date_format returns 00/00/0000. This is correct, but how can I make it so that it returns NULL when the input is NULL?

amosrivera · Accepted Answer · 2011-04-07 05:43:56Z

16

SELECT IF(mydate,date_format(mydate, '%d/%m/%Y'),NULL) FROM xyz;

Source: http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html

answered Apr 7, 2011 at 5:43

amosrivera

26.6k9 gold badges70 silver badges76 bronze badges

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

Comments

Bjoern · Accepted Answer · 2011-04-07 05:47:23Z

1

You can wrap this into a IF-Clause, like this:

SELECT IF(mydate,DATE_FORMAT(mydate, '%d/%m/%Y'),NULL) FROM xyz;

That said, if your variable mydate is not a date value, the query in your post should return (NULL) anyway.

answered Apr 7, 2011 at 5:47

Bjoern

16.3k4 gold badges47 silver badges50 bronze badges

Comments

Francisco Alvarado · Accepted Answer · 2011-04-07 05:48:55Z

0

select case when isnull(mydate) then null else date_format(mydate, '%d/%m/%Y') end as mydate from xyz;

answered Apr 7, 2011 at 5:48

Francisco Alvarado

2,7982 gold badges27 silver badges51 bronze badges

Comments

j0k · Accepted Answer · 2012-08-14 16:24:11Z

0

case 
    when date_format(mydate, '%d/%m/%Y') = 00/00/0000 then null
    else ///
end as mydate,

edited Aug 14, 2012 at 16:24

j0k

22.8k28 gold badges81 silver badges90 bronze badges

answered Apr 7, 2011 at 5:48

Light

254 bronze badges

Collectives™ on Stack Overflow

Return null for date_format when input is null in mysql

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related