Comparison of common Oracle, sqlserver, and MySQL functions [to_char, to_number, to_date]

Oracle --> MySQL

To_char (sysdate, 'yyyy-mm-dd') --> date_format (sysdate (), '% Y-% m-% D ');
To_date (sysdate, 'yyyy-mm-dd') --> str_to_date (sysdate (), '% Y-% m-% D ');

1. Oracle

(1) Use the to_char function to process numbers

To_char (number, 'format ')

To_char (salary, '$99,999.99 ');

(2) Use the to_char function to process the date
To_char (date, 'format ');
To_char (sysdate, 'q') quarter
To_char (sysdate, 'yyyy') year
To_char (sysdate, 'mm') month
To_char (sysdate, 'dd ')
To_char (sysdate, 'D') day of the week
To_char (sysdate, 'day') day of the week
To_char (sysdate, 'ddd ') day of the year

(3) to_number
Use the to_number function to convert characters to numbers.
To_number (char [, 'format'])
(4) to_date
Use the to_date function to convert a character to a date
To_date (char [, 'format'])

Bytes ------------------------------------------------------------------------------------------

2. SQL

1) type conversion: Cast () type conversion, for example, cast (@ startdate as datetime), cast (name as nvarchar)
2) date truncation: datepart () returns an integer representing the specified date part of the specified date, for example:
datepart (DD, @ startdate) -- take the day,
datepart (month, @ startdate) -- Take the month,
datepart (YY, @ startdate) -- year

3) date operation: dateadd () returns a new datetime type based on a period of time added to a date. Example: Add 2 days dateadd (day, 2, '2017-6-1 ') and return: 2009 00:00:00. 000

Bytes ------------------------------------------------------------------------------------------

3. MySQL

Date Format Conversion: date_format (date, format), time_format (time, Format)

1) Use date_format (database field, '% Y % m % D') to format the time field in the string format of "yyyy-mm-dd"

2) str_to_date (database field, '% m. % d. % y') Format of string type field to "yyyy-mm-dd" Time Format

3) mysql> select date_format ('2017-08-08 22:23:00 ',' % w % m % y ');

+ ------------------------------------------------ +
| Date_format ('2017-08-08 22:23:00 ',' % w % m % y') |
+ ------------------------------------------------ +
| Friday August 2008 |
+ ------------------------------------------------ +

Mysql> select date_format ('2017-08-08 22:23:01 ',' % Y % m % d % H % I % s ');

+ ---------------------------------------------------- +
| Date_format ('2017-08-08 22:23:01 ',' % Y % m % d % H % I % s') |
+ ---------------------------------------------------- +
| 1, 20080808222301 |
+ ---------------------------------------------------- +

Mysql> select time_format ('22: 23: 01', '% H. % I. % s ');

+ ------------------------------------- +
| Time_format ('22: 23: 01', '% H. % I. % s') |
+ ------------------------------------- +
| 22.23.01 |
+ ------------------------------------- +

MySQL Date and Time conversion functions: date_format (date, format), time_format (time, format) can convert a date/time to a variety of string formats. It is an inverse conversion of the str_to_date (STR, format) function.

4)

 
To_number can be set to cast, but an appropriate type is required. For example: Select cast (11 as unsigned INT)/* integer */select cast (11 as decimal )) /* float type */