Use of the time format conversion function convert () in SQL Server

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

Convert is a date conversion function, which is typically used when the time type (Datetime,smalldatetime) and the string type (Nchar,nvarchar,char,varchar) are converted to each other. The 3 parameters of the function: The 1th argument is the converted large, the 2nd is the field or function of the converted date, and the 3rd is the converted format.

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 (), $): 10:57:49:513am
SELECT CONVERT (varchar), GETDATE (), 131): 18/04/1427 10:57:49:920am

First, date and time functions in SQL Server

1. GetDate () returns the current date and time of the system
Select GETDATE ()

2.DATEADD (interval,number,date) adds a period of time number to a specified date in the interval specified way, returning a new datetime value

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 (INTERVAL,DATE1,DATE2) returns the difference between Date2 and date1 two dates in interval specified way

Select DateDiff (Day, ' 2004-09-01 ', ' 2004-09-18 ')--return: 17

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

4. DatePart (interval,date) returns the integer value corresponding to the specified portion of the date, interval

Select DATEPART (Month, ' 2004-10-15 ')--return 10

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

5. Datename (interval,date) returns the name of the string that corresponds to the specified part of the date, interval

Select Datename (Weekday, ' 2004-10-15 ')--return: Friday

Abbreviation
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

Second, date format conversion

Select CONVERT (varchar (11), time,) as time,* from TableName

Select Replace (replace (CONVERT (varchar, GETDATE (), 120), '-', '), ', '), ': ', ')--20040912110608

Select Test.date from Test where CONVERT (VARCHAR (), test.date,120) = ' 2015-08-09 '

Use of the time format conversion function convert () in SQL Server