SQL Date Function Summary

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) 3 parameters of the function that are used when converting to each other, the 1th parameter is the converted size, the 2nd is the field or function for 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) 14 | 114 | Hh:mi:ss:mmm (24-hour) 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 example: 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): Select CONVERT (varchar), GETDATE (), 7): All, select CONVERT (varchar), G Etdate (), 8): 10:57:46 select CONVERT (varchar, GETDATE (), 9): 10:57:46:827am Select CONVERT (varchar (100), GETDATE (): 05-16-11 select CONVERT (varchar), GETDATE (), one): 11/05/16 select CONVERT (varchar), GETDATE (), 12 ): 110516 Select CONVERT (varchar), GETDATE (), (+): 10:57:46:937 select CONVERT (varchar), GETDATE (), 14) : 10:57:46:967 Select CONVERT (varchar), GETDATE (): 2011-05-16 10:57:47 Select CONVERT (varchar), GETDATE (), 2 1): 2011-05-16 10:57:47.157 Select CONVERT (varchar (), GETDATE (), (): 05/16/11 10:57:47 AM SELECT convert (varchar (100) , GETDATE (), max): 2011-05-16 select CONVERT (varchar), GETDATE (): 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/201 1

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 (), Max.): 16-05-2011 Select CONVERT (varchar), GETDATE (), 106): + select CONVERT (varchar), GETDATE (), 107): (+)-select Convert (varchar), GETDATE (), 108): 10:57:49 select CONVERT (varchar), GETDATE (), 109): 10:57:49:437am Select CO Nvert (varchar), GETDATE (), 110): 05-16-2011

Select CONVERT (varchar), GETDATE (), 111): 2011/05/16 select CONVERT (varchar), GETDATE (), (): 20110516 Select CO Nvert (varchar), GETDATE (), 113): 10:57:49:513 SELECT CONVERT (varchar (+), GETDATE (),%): 10:57:49:547 SE Lect CONVERT (varchar), GETDATE (), +): 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 (100), GETDATE (), ():???? ?????? 1427 10:57:49:907am SELECT CONVERT (varchar (+), GETDATE (), 131): 18/04/1427 10:57:49:920am

Freq used: Select CONVERT (varchar), GETDATE (), 8): 10:57:46 select CONVERT (varchar), GETDATE (): 10:57:47 Select CONV ERT (varchar), GETDATE (), 108): 10:57:49 select CONVERT (varchar), GETDATE (), (): 110516 select CONVERT (varchar ( , GETDATE (), 23): 2011-05-16

---Common instance operations:

One, SQL Server datetime function in SQL Server, date and time function 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 for 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 portion 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 this year how many weeks =datename (week, ' 2004-10-15 '), today is the week of =datename (weekday, ' 2004-10-15 ')

Second, date format conversion select CONVERT (varchar, GETDATE (),--2004-09-12) 11:06:08 Select replace (replace (replace (CONVERT (V Archar, GETDATE (), +), '-', '), ' ', '), ': ', ')--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 ') with a return 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 drawings function parameter/function GetDate () return system The current date and time DateDiff (INTERVAL,DATE1,DATE2) returns the difference between Date2 and date1 two dates date2-date1 DateAdd (interval,number, in the interval specified manner) Date) The integer value Datename (interval,date) that is specified in interval, plus number after number DatePart (interval,date) returns the date, interval the specified part. Returns the string name corresponding 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 description year Yy yyyy 1753 ~ 9999 Quarter Qq Q season 1 ~ 4 month Mm M 1 ~ Day of yearly Dy y Number of days of the year 1-366 day Dd D days, 1-31 Weekday Dw W Day of the week, the day of the week 1-7 Week Wk WW Week, the first week of the year 0 ~ Hour Hh h 0 ~ Min Ute Mi N min 0 ~ Second Ss s sec 0 ~ Millisecond ms-MSEC 0 ~ 999

SQL Date Function Summary