MySQL 날짜 함수 에러 ?

안녕하세요 거북탄토끼 입니다  

 

아래와 같은 3개 예제 가 있습니다 .

 

1

root@mysql3357.sock>[information_schema]>select str_to_Date('1992-04-01','%Y-%m-%d')-str_to_Date('1992-04-01','%Y-%m-%d') dt ;
+------+
| dt   |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

2

root@mysql3357.sock>[information_schema]>select str_to_Date('1992-04-11','%Y-%m-%d')-str_to_Date('1992-04-1','%Y-%m-%d') dt ;
+------+
| dt   |
+------+
|   10 |
+------+
1 row in set (0.00 sec)

3.

root@mysql3357.sock>[performance_schema]>select str_to_Date('1992-04-01','%Y-%m-%d')-str_to_Date('1992-03-31','%Y-%m-%d') dt ;
+------+
| dt   |
+------+
|   70 |
+------+
1 row in set (0.00 sec)

 

위에 1,2,3 3개 SQL 은 MySQL 사용시 자주 부딪치는 문제입니다  

특히 Oracle -> MySQL 프로젝트시 많이 접해볼수 있습니다 .

 

위에 내용은 아래 쿼리와 동일 합니다  

root@mysql3357.sock>[performance_schema]>select str_to_Date('2020-04-01','%Y-%m-%d')-str_to_Date('2020-03-31','%Y-%m-%d') dt ;
+------+
| dt   |
+------+
|   70 |
+------+
1 row in set (0.00 sec)

root@mysql3357.sock>[performance_schema]>select 20200401-20200331;
+-------------------+
| 20200401-20200331 |
+-------------------+
|                70 |
+-------------------+
1 row in set (0.00 sec)

 

이러한 문제를 해결하려면 아래 와같이 해도 됩니다 

root@mysql3357.sock>[information_schema]>select to_days('2020-04-11')-to_days('2020-04-1');
+--------------------------------------------+
| to_days('2020-04-11')-to_days('2020-04-1') |
+--------------------------------------------+
|                                         10 |
+--------------------------------------------+

root@mysql3357.sock>[performance_schema]>select to_days('2020-04-01')-to_days('2020-03-31');
+---------------------------------------------+
| to_days('2020-04-01')-to_days('2020-03-31') |
+---------------------------------------------+
|                                           1 |
+---------------------------------------------+

 

감사합니다