SQL Time conversion format CONVERT (varchar (10), field name, conversion format)

CONVERT (varchar (10), field name, conversion format)

CONVERT (nvarchar), count_time,121
Convert is a date conversion function, typically in the time type (Datetime,smalldatetime) and the string type (Nchar,nvarchar,char,varchar)
The 3 parameters of the function are used when converting to each other, the 1th parameter is the converted size, the 2nd is the field or function of the conversion date, and the 3rd is the converted format.

Specific as follows:
0 | 0 or 100 | Mon dd yyyy hh:miam (or PM)
1 | 101 | Mm/dd/yy
2 | 102 | Yy-mm-dd
3 | 103 | Dd/mm/yy
4 | 104 | Dd-mm-yy
5 | 105 | Dd-mm-yy
6 | 106 | DD Mon yy
7 | 107 | Mon dd,yy
8 | 108 | Hh:mm:ss
9 | 9 or 109 | Mon dd yyyy Hh:mi:ss:mmmmAM (or PM)
10 | 110 | Mm-dd-yy
11 | 111 | Yy/mm/dd
12 | 112 | Yymmdd
11| or 113 | DD Mon yyyy hh:mi:ss:mmm (24 hour system)
14 | 114 | Hh:mi:ss:mmm (24-hour system)
15 | or 120 | Yyyy-mm-dd Hh:mi:ss (24-hour system)
16 | or 121 | Yyyy-mm-dd Hh:mi:ss:mmm (24-hour system)
Specific examples:
SELECT CONVERT (varchar), GETDATE (), 0): 10:57am
SELECT CONVERT (varchar), GETDATE (), 1): 05/16/11

SELECT CONVERT (varchar), GETDATE (), 2): 11.05.16
SELECT CONVERT (varchar), GETDATE (), 3): 16/05/11

SELECT CONVERT (varchar), GETDATE (), 4): 16.05.11
SELECT CONVERT (varchar), GETDATE (), 5): 16-05-11
SELECT CONVERT (varchar), GETDATE (), 6): 16 05 11
SELECT CONVERT (varchar), GETDATE (), 7): 05 16, 11
SELECT CONVERT (varchar), GETDATE (), 8): 10:57:46
SELECT CONVERT (varchar), GETDATE (), 9): 10:57:46:827am
SELECT CONVERT (varchar), GETDATE (), 10): 05-16-11
SELECT CONVERT (varchar), GETDATE (), 11): 11/05/16
SELECT CONVERT (varchar), GETDATE (), 12): 110516
SELECT CONVERT (varchar), GETDATE (), 13): 16 05 2011 10:57:46:937
SELECT CONVERT (varchar), GETDATE (), 14): 10:57:46:967
SELECT CONVERT (varchar), GETDATE (), 20): 2011-05-16 10:57:47
SELECT CONVERT (varchar), GETDATE (), 21): 2011-05-16 10:57:47.157
SELECT CONVERT (varchar), GETDATE (): 05/16/11 10:57:47 AM
SELECT CONVERT (varchar), GETDATE (), 23): 2011-05-16
SELECT CONVERT (varchar), GETDATE (), 24): 10:57:47
SELECT CONVERT (varchar), GETDATE (), 25): 2011-05-16 10:57:47.250
SELECT CONVERT (varchar), GETDATE (), (+): 10:57am
SELECT CONVERT (varchar), GETDATE (), 101): 05/16/2011

SELECT CONVERT (varchar), GETDATE (), 102): 2011.05.16
SELECT CONVERT (varchar), GETDATE (), 103): 16/05/2011

Select CONVERT (varchar), GETDATE (), 104): 16.05.2011
SELECT CONVERT (varchar), GETDATE (), 105): 16-05-2011
Select CONVERT (varchar), GETDATE (), 106): (+)
Select CONVERT (varchar, GETDATE (), 107): In the
Select CONVERT (varchar), GETDATE (), 108): 10:57:49
SELECT CONVERT (varchar), GETDATE (), 109): 10:57:49:437am
SELECT CONVERT (varchar), GETDATE (), (): 05-16-2011

SELECT CONVERT (varchar), GETDATE (), 111): 2011/05/16
SELECT CONVERT (varchar), GETDATE (), 112): 20110516
SELECT CONVERT (varchar), GETDATE (), 113): 16 05 2011 10:57:49:513
SELECT CONVERT (varchar), GETDATE (), 114): 10:57:49:547
SELECT CONVERT (varchar), GETDATE (), 120): 2011-05-16 10:57:49
SELECT CONVERT (varchar), GETDATE (), 121): 2011-05-16 10:57:49.700
SELECT CONVERT (varchar), GETDATE (), 126): 2011-05-16t10:57:49.827
SELECT CONVERT (varchar), GETDATE (), ():???? ?????? 1427 10:57:49:907am
SELECT CONVERT (varchar), GETDATE (), 131): 18/04/1427 10:57:49:920am

Common:
Select CONVERT (varchar), GETDATE (), 8): 10:57:46
Select CONVERT (varchar), GETDATE (), 24): 10:57:47
Select CONVERT (varchar), GETDATE (), 108): 10:57:49
Select CONVERT (varchar), GETDATE (), 12): 110516
Select CONVERT (varchar), GETDATE (), 23): 2011-05-16

---Common instance operations:

One, SQL Server date-time functions
Date and time functions in SQL Server
1. Current system date, time
Select GETDATE ()

2. DateAdd returns a new datetime value based on adding a period of time to the specified date
Example: Add 2 days to the date
Select DATEADD (day,2, ' 2010-10-15 ')--return: 2010-10-17 00:00:00.000

And so on: it is also possible to reverse the date for a few days or months.

Select DATEADD (day,-2, ' 2010-10-15 ')--return: 2010-10-13 00:00:00.000

Select DATEADD (month,-2, ' 2010-10-15 ')--return: 2010-08-1 00:00:00.000

3. DateDiff returns the number of date and time boundaries across two specified dates.
Select DateDiff (Day, ' 2004-09-01 ', ' 2004-09-18 ')--return: 17

4. DatePart returns an integer representing the specified date portion of the specified date.
Select DATEPART (Month, ' 2004-10-15 ')--return 10

5. Datename returns a string representing the specified date part of the specified date
Select Datename (Weekday, ' 2004-10-15 ')--return: Friday

6. Day (), month (), year ()--can be compared with datepart

Select Current date =convert (varchar), GETDATE (), 120)
, Current time =convert (varchar (8), GETDATE (), 114)

Select Datename (DW, ' 2004-10-15 ')

Select how many weeks of the year =datename (week, ' 2004-10-15 ')
, today is a few weeks =datename (weekday, ' 2004-10-15 ')

Second, date format conversion
Select CONVERT (varchar, GETDATE (), 120)--2004-09-12 11:06:08
Select Replace (replace (CONVERT (varchar, GETDATE (), 120), '-', '), ', '), ': ', ')--20040912110608

Select CONVERT (varchar), GETDATE (), 111)
2004/09/12

Select CONVERT (varchar), GETDATE (), 112)
20040912

Select CONVERT (varchar), GETDATE (), 102)
2004.09.12

Other infrequently used date format conversion methods:

Select CONVERT (varchar), GETDATE (), 101)
09/12/2004

Select CONVERT (varchar), GETDATE (), 103)
12/09/2004

Select CONVERT (varchar), GETDATE (), 104)
12.09.2004

Select CONVERT (varchar), GETDATE (), 105)
12-09-2004

Select CONVERT (varchar), GETDATE (), 106)
12 09 2004

Select CONVERT (varchar), GETDATE (), 107)
09 12, 2004

Select CONVERT (varchar), GETDATE (), 108)
11:06:08

Select CONVERT (varchar), GETDATE (), 109)
09 12 2004 1

Select CONVERT (varchar), GETDATE (), 110)
09-12-2004

Select CONVERT (varchar), GETDATE (), 113)
12 09 2004 1

Select CONVERT (varchar), GETDATE (), 114)
11:06:08.177
Example:
1.GetDate () for SQL Server:select GetDate ()

2.DateDiff (' s ', ' 2005-07-20 ', ' 2005-7-25 22:56:32 ') returns a value of 514,592 seconds
DateDiff (' d ', ' 2005-07-20 ', ' 2005-7-25 22:56:32 ') returns a value of 5 days

3.DatePart (' W ', ' 2005-7-25 22:56:32 ') returns a value of 2 that is Monday (Sunday is 1, Saturday is 7)
DatePart (' d ', ' 2005-7-25 22:56:32 ') returns a value of 25, or 25th
DatePart (' y ', ' 2005-7-25 22:56:32 ') returns a value of 206 that is the No. 206 Day of the Year
DatePart (' yyyy ', ' 2005-7-25 22:56:32 ') returns a value of 2005 that is 2005
Figures
function parameters/Functions
GetDate () returns the current date and time of the system
DateDiff (INTERVAL,DATE1,DATE2) returns the difference between Date2 and date1 two dates in the interval specified date2-date1
DATEADD (interval,number,date) with the date specified in interval, plus number
DatePart (interval,date) returns the integer value corresponding to the specified portion of the date, interval
Datename (interval,date) returns the name of the string that corresponds to the specified part of the date, interval

The setting values for the parameter interval are as follows:

Value abbreviations (SQL Server) Access and ASP descriptions
Year Yy yyyy 1753 ~ 9999
Quarter Qq Q Season 1 ~ 4
Month Mm M 1 ~ 12
Day of the year Dy y the number of days of the year, the day of the first 1-366
Day Dd D, 1-31
Weekday Dw W Day of the week, day of the week 1-7
Week Wk WW Week, week of the year 0 ~ 51
Hour Hh H 0 ~ 23
Minute Mi N min 0 ~ 59
Second Ss s seconds 0 ~ 59
Millisecond MS-MS 0 ~ 999

Source: http://hi.baidu.com/smilejianai/item/6657bd4392510415886d1010