0. Date --> String
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM dual;
---->
mysql> SELECT DATE_FORMAT(SYSDATE(), '%Y-%m-%d %h:%m:%s');
+---------------------------------------------+
| DATE_FORMAT(SYSDATE(), '%Y-%m-%d %h:%m:%s') |
+---------------------------------------------+
| 2016-06-24 03:06:36 |
+---------------------------------------------+
1 row in set (0.00 sec)
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') FROM dual;
---->
mysql> SELECT DATE_FORMAT(SYSDATE(), '%d-%b-%Y') ;
+------------------------------------+
| DATE_FORMAT(SYSDATE(), '%d-%b-%Y') |
+------------------------------------+
| 24-Jun-2016 |
+------------------------------------+
1 row in set (0.01 sec)
|
Oracle |
|
MySQL |
|
YYYY |
4-digit year |
%Y |
|
YY |
2-digit year |
%y |
|
RRRR |
2 or 4-digit year, 20th century for 00-49 |
%Y |
|
RR |
2-digit year, 20th century for 00-49 |
%y |
|
MON |
Abbreviated month (Jan - Dec) |
%b |
|
MONTH |
Month name (January - December) |
%M |
|
MM |
Month (1 - 12) |
%m |
|
DY |
Abbreviated day (Sun - Sat) |
%a |
|
DD |
Day (1 - 31) |
%d |
|
HH24 |
Hour (0 - 23) |
%H |
|
HH or HH12 |
Hour (1 - 12) |
%h |
|
MI |
Minutes (0 - 59) |
%i |
|
SS |
Seconds (0 - 59) |
%s |
1. Number --> String
SELECT to_char(10000,'9,999,999') FROM dual ;
---->
mysql> SELECT FORMAT(10000,0) ;
+-----------------+
| FORMAT(10000,0) |
+-----------------+
| 10,000 |
+-----------------+
1 row in set (0.00 sec)
mysql> SELECT FORMAT(10000,1) ;
+-----------------+
| FORMAT(10000,1) |
+-----------------+
| 10,000.0 |
+-----------------+
1 row in set (0.00 sec)
mysql> SELECT FORMAT(10000,2) ;
+-----------------+
| FORMAT(10000,2) |
+-----------------+
| 10,000.00 |
+-----------------+
1 row in set (0.00 sec)
mysql> SELECT concat('String To Number ' , cast(2 as char) ) ;
+------------------------------------------------+
| concat('String To Number ' , cast(2 as char) ) |
+------------------------------------------------+
| String To Number 2 |
+------------------------------------------------+
1 row in set (0.00 sec)
2. String --> DATE
SELECT TO_DATE('2013-02-11', 'YYYY-MM-DD') FROM dual;
---->
mysql> SELECT STR_TO_DATE('2013-02-11', '%Y-%m-%d');
+---------------------------------------+
| STR_TO_DATE('2013-02-11', '%Y-%m-%d') |
+---------------------------------------+
| 2013-02-11 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> select O_ORDERDATE from ORDERS where O_ORDERDATE between cast('1992-01-01' as date) and cast('1992-01-02' as date) limit 10 ;
+-------------+
| O_ORDERDATE |
+-------------+
| 1992-01-01 |
| 1992-01-01 |
| 1992-01-01 |
| 1992-01-01 |
| 1992-01-01 |
| 1992-01-01 |
| 1992-01-01 |
| 1992-01-01 |
| 1992-01-01 |
| 1992-01-01 |
+-------------+
10 rows in set (0.00 sec)
3. String --> Number
SELECT TO_NUMBER('123') FROM dual ;
---->
mysql> create table Tchar ( num char(5) ) ;
mysql> insert into Tchar values ('100' ) ;
mysql> insert into Tchar values ('200' ) ;
mysql> SELECT * FROM Tchar WHERE cast(num as signed) > 120 ;
+------+
| num |
+------+
| 200 |
+------+
1 row in set (0.00 sec)