CODEWITHSUNDEEP
mysql
Rahul Sharma
Rahul Sharma

Reputation: 622

MYSQL: DATE function returning null in Select query

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:

enter image description here

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

enter image description here

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

Structure View

enter image description here

Upvotes: 3

Views: 2040

Answers (2)

P.Salmon
P.Salmon

Reputation: 17665

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)

Upvotes: 1

Srihari Karanth
Srihari Karanth

Reputation: 2167

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

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

Upvotes: 2

Related Questions