Format CONVERT date and time in SQL-mysql tutorial

Source: Internet
Author: User
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.