MYSQL: DATE function returning null in Select query

Question

i have a data with all column types as text even date also stored in text type column. Now when i am using select query to access the column its working find but when i am trying to perform any date operation on that column it always return null.

Table:

if am running query select column_14 from mytable where id = 2 its working fine but when i am trying to find the difference between today date and column_14 its always return null

Query Running:

select DATEDIFF(NOW(),STR_TO_DATE(column_14,"%y-%m-%d")) from `mytable` where id = 2

can anyone please tell me what is wrong in my query ?

Structure View

your date column have y-m-d format, so try once like, DATEDIFF(NOW(),column_14) without DATE(). — Mr. J
– Mr. J, Commented Apr 25, 2017 at 12:33
i got the problem..there is mistake in my data. I just export a single row and its have \0 with date and after REPLACE that i am able to perform date operations..thanks — Rahul Sharma
– Rahul Sharma, Commented Apr 26, 2017 at 7:23

Srihari Karanth · Accepted Answer · 2017-04-25 12:23:48Z

2

It should be %Y instead of %y. %Y takes 4 digit year.

DATEDIFF(NOW(),STR_TO_DATE(column_14,"%Y-%m-%d"))

answered Apr 25, 2017 at 12:23

Srihari Karanth

2,1772 gold badges26 silver badges40 bronze badges

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

7 Comments

Rahul Sharma Over a year ago

i agree with you but it always returns null

Rahul Sharma Over a year ago

even if i am using simple date() it returns null

Rahul Sharma Over a year ago

its just a header

Srihari Karanth Over a year ago

Can you update the question to show the whole row including id = 2.

Srihari Karanth Over a year ago

A simple select DATEDIFF(NOW(),STR_TO_DATE('2010-10-11','%Y-%m-%d')); gives proper date difference, must be something to do with the table or table name. Rest all seems to be fine.

|

P.Salmon · Accepted Answer · 2017-04-25 12:45:54Z

1

Str_to_date will return null values if you pick the wrong format or the function detects an out of range month or day number. In your example you have picked a wrong format %y should be %Y. Here are some samples of what can happen. (nb: str_to_date is poor at this and doesn't check how many days are valid for a month)

MariaDB [sandbox]> create table t (dt text);
Query OK, 0 rows affected (0.28 sec)

MariaDB [sandbox]> insert into t values ('2017-13-01'),('2017-04-31'),('2017-04-33'),('2017-04-03'),('birth_date');
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0

MariaDB [sandbox]>
MariaDB [sandbox]> select str_to_date(dt,'%y-%m-%d') Invalid_year_format,
    -> str_to_date(dt,'%Y-%m-%d') Valid_year_format_MM_and_DD,
    -> datediff(now(),str_to_date(dt,'%Y-%m-%d')) datediff_examples
    -> from t;
+---------------------+-----------------------------+-------------------+
| Invalid_year_format | Valid_year_format_MM_and_DD | datediff_examples |
+---------------------+-----------------------------+-------------------+
| NULL                | NULL                        |              NULL |
| NULL                | 2017-04-31                  |                -6 |
| NULL                | NULL                        |              NULL |
| NULL                | 2017-04-03                  |                22 |
| NULL                | NULL                        |              NULL |
+---------------------+-----------------------------+-------------------+
5 rows in set, 11 warnings (0.00 sec)

answered Apr 25, 2017 at 12:45

P.Salmon

17.7k2 gold badges14 silver badges22 bronze badges

3 Comments

Rahul Sharma Over a year ago

getting still same output...even after correct %Y-%m-%d this

P.Salmon Over a year ago

There is no difference if the table is myisam or innodb. As I said problem is with data not function.

Rahul Sharma Over a year ago

i got the problem..there is mistake in my data. I just export a single row and its have \0 with date and after REPLACE that i am able to perform date operations..thanks

Collectives™ on Stack Overflow

MYSQL: DATE function returning null in Select query

2 Answers 2

7 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related