In mysql, we often use the CONVERT () function to format the time and date of the CONVERT type. below I will introduce you to the usage of the CONVERT () function in detail, for your reference.
In mysql, we often use the CONVERT () function to format the time and date of the CONVERT type. below I will introduce you to the usage of the CONVERT () function in detail, for your reference.
Format:
CONVERT (data_type, expression [, style])
Note:
This style is generally in the time type (datetime, smalldatetime) and string type (nchar, nvarchar, char, varchar)
It is used only when they are converted to each other.
Example:
The code is as follows: |
|
Select convert (varchar (30), getdate (), 101) now
|
Result:
Now
| 09/15/2001
The code is as follows: |
|
Select CONVERT (varchar (100), GETDATE (), 0) -- 05 16 2006 AM Select CONVERT (varchar (100), GETDATE (), 1) -- 05/16/06 Select CONVERT (varchar (100), GETDATE (), 2) -- 06.05.16 Select CONVERT (varchar (100), GETDATE (), 3) -- 16/05/06 Select CONVERT (varchar (100), GETDATE (), 4) -- 16.05.06 Select CONVERT (varchar (100), GETDATE (), 5) -- 16-05-06 Select CONVERT (varchar (100), GETDATE (), 6) -- 16 05 06 Select CONVERT (varchar (100), GETDATE (), 7) -- 05 16, 06 Select CONVERT (varchar (100), GETDATE (), 8) -- 10:57:46 Select CONVERT (varchar (100), GETDATE (), 9) -- 05 16 2006 10: 57: 46: 827AM Select CONVERT (varchar (100), GETDATE (), 10) -- 05-16-06 Select CONVERT (varchar (100), GETDATE (), 11) -- 06/05/16 Select CONVERT (varchar (100), GETDATE (), 12) -- 060516 Select CONVERT (varchar (100), GETDATE (), 13) -- 16 05 2006 10: 57: 46: 937 Select CONVERT (varchar (100), GETDATE (), 14) -- 10: 57: 46: 967 |
The conversion time of a style number is as follows:
Style (2 bits indicate the year) |
Style (4-digit indicates the year) |
Input/output format |
0 |
100 |
Mon dd yyyy hh: miAM (or PM) |
1 |
101 |
Mm/dd/yyyy |
2 |
102 |
Yyyy-mm-dd |
3 |
103 |
Dd/mm/yyyy |
4 |
104 |
Dd-mm-yyyy |
5 |
105 |
Dd-mm-yyyy |
6 |
106 |
Dd mon yyyy |
7 |
107 |
Mon dd, yyyy |
8 |
108 |
Hh: mm: ss |
9 |
109 |
Mon dd yyyy hh: mi: ss: mmmmAM (or PM) |
10 |
110 |
Mm-dd-yy |
11 |
111 |
Yy/mm/dd |
12 |
112 |
Yymmdd |
13 |
113 |
Dd mon yyyy hh: mi: ss: mmm (in 24-hour format) |
14 |
114 |
Hh: mi: ss: mmm (in 24-hour format) |
20 |
120 |
Yyyy-mm-dd hh: mi: ss (in 24-hour format) |
21 |
121 |
Yyyy-mm-dd hh: mi: ss: mmm (in 24-hour format) |
Note:Convert differs from cast. here is a brief description.
Convert is generally used for conversion between decimal places;
Cast is generally used for decimal to numeric and numeric