SQL Server Date function

------------------date is converted into a month and a day and seconds--------------- Select ' R ' +convert (varchar), GETDATE (), 112) +right (CAST (Power (10,2) as varchar) +datepart (hour, GETDATE ()), 2) +right (CAST (Power (10,2) as varchar) +datepart (minute, GETDATE ()), 2) +right (CAST (Power (10,2) as varchar) +datepart (SS, GETDATE ()), 2) +right (CAST (Power (10,3) as varchar) +datepart (MS, GETDATE ()), 3) SQL Server time Date function detailed, SQL Server, time date, 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, ' 2004-10-15 ')--return: 2004-10-17 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 Select DateDiff (Day, ' 2004-09-18 ', ' 2004-09-01 ')--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 ') 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 way 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: Description of the value abbreviation (SQL Server) (Access and ASP) 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 Access and ASP Use Date () and now () to obtain system datetime, where Datediff,dateadd,datepart is also used to In Access and ASP, the use of these functions is similar 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 The specific syntax: Date functions are used to manipulate data of datetime and smalldatetime types and perform arithmetic operations. As with other functions, you can The SELECT and WHERE clauses of the SELECT statement are used with the date function in an expression. Here's how to use it: A date function parameter in which the number of arguments should be different from the function. · Day () The day () function syntax is as follows: Day (<date_expression>) The day () function returns the date value in Date_expression. · MONTH () The MONTH () function syntax is as follows: MONTH (<date_expression>) The month () function returns the months value in Date_expression. Unlike the day () function, an integer value of 1 is returned when the argument to the MONTH () function is an integer, that is, SQL Server considers its is January 1900. · Year () The year () function syntax is as follows: Year (<date_expression>) The year () function returns the years value in Date_expression. Reminder: When using a date function, its date value should be between 1753 and 9999, which is the date that the SQL Server system can recognize Otherwise, errors may occur. · DATEADD () The DATEADD () function syntax is as follows: DATEADD (<DATEPART>, <number>, <date>) The DATEADD () function returns a new date that is generated by the specified date, plus the specified extra date interval number. Parameter "DatePart Often used in date functions, it is used to specify the components that make up the date type data, such as year, quarter, month, day, week, and so on. The values are as shown in table 4-9: · DATEDIFF () The DATEDIFF () function syntax is as follows: DATEDIFF (<DATEPART>, <date1>, <date2>) The DATEDIFF () function returns the difference in the datepart aspect of the two specified date, that is, the date2 exceeds the date1 gap value, which The result value is an integer value with a positive sign. For different datepart, the maximum gap value allowed by the DATEDIFF () function is not , such as: When DatePart is second, the maximum gap value allowed by the DATEDIFF () function is 68: The year datepart is Millisecond, the maximum gap allowed by the DATEDIFF () function is 24 days, 20 hours, 30 minutes, 23 seconds, 647 milliseconds. · Datename () The Datename () function syntax is as follows: Datename (<DATEPART>, <date) > The Datename () function returns the specified portion of the date as a string in this section. specified by DatePart. · DATEPART () The DATEPART () function syntax is as follows: DATEPART (<DATEPART>, <date>) The DATEPART () function returns the specified part of the date as an integer value. This section is specified by DatePart. DATEPART (dd, date) is equivalent to day (date) DATEPART (mm, date) equals month (date) DATEPART (yy, date) is equivalent to year (date) · GETDATE () The GETDATE () function syntax is as follows: GETDATE () The GETDATE () function returns the current date and time of the system in the default format of DateTime, which is often used as a parameter to other functions or commands Number of uses. In the development database application, you often encounter problems with processing time, such as querying the records for a specified time. Here are some common questions , combined with some of their own experience, and everyone to discuss this kind of problem. Let's start by introducing the usage of several main functions of processing time in SQL Server: GETDATE () function: Gets the current date and time of the system. The return value is of type DateTime. Usage: getdate () Example: Select GETDATE () as Dte,dateadd (Day,-1,getdate ()) as Nowdat Output Result: DTE Nowdat 1999-11-21 19:13:10.083 1999-11-20 19:13:10.083 (1 row (s) affected) DatePart () Function: Returns the specified part of the time as an integer. Usage: DATEPART (datepart,date) Parameter description: The part of the time to return when datepart, used to value year, month, day, hour, minute. The date is the specified time. Example: SELECT DATEPART (month, GETDATE ()) as ' month number ' Output Result: Month number 11 (1 row (s) affected) DATEADD () Function: Returns a new time value by adding an integer value to the specified part of the specified time. Usage: DATEADD (datepart,number,date) Parameter description: DatePart (IBID.) Date (IBID.) Number to increment value, integer, positive negative, positive value returns the time values after date, negative value returns date The previous time value Example: Select GETDATE () as Today Select DATEADD (Day,-1,getdate ()) Select DATEADD (Day,1,getdate ()) Output: Today 1999-11-21 19:42:41.410 (1 row (s) affected) Yesterday 1999-11-20 19:42:41.410 (1 row (s) affected) Tomorrow 1999-11-22 19:42:41.410 (1 row (s) affected) DateDiff () Function: Returns a difference of two time to the specified time portion. Returns an integer value. such as 1991-6-12 and 1991-6-21 Between the days To calculate the difference of 9 days, 1998-6-12 and 1999-6-23 by year difference 1 years, 1999-12-1 and 1999-3-12 by month difference 9 months Usage: DateDiff (DAREPART,DATE1,DATE2) Parameter description: DatePart (IBID.) Date1, Date2 (ibid. date) Example: Select DateDiff (Month, ' 1991-6-12 ', ' 1992-6-21 ') as a Syntax convert (varchar), GetDate (), style) The style value that can be used: Style ID Style Format 100 or 0 Mon dd yyyy hh:miam (or PM) 101 Mm/dd/yy 102 Yy.mm.dd 103 Dd/mm/yy 104 Dd.mm.yy 105 Dd-mm-yy 106 DD Mon yy 107 Mon DD, yy 108 Hh:mm:ss 109 or 9 Mon dd yyyy hh:mi:ss:mmmAM (or PM) 110 Mm-dd-yy 111 Yy/mm/dd 112 Yymmdd 113 or 13 DD Mon yyyy hh:mm:ss:mmm (24h) 114 Hh:mi:ss:mmm (24h) 120 or 20 Yyyy-mm-dd Hh:mi:ss (24h) 121 or 21 Yyyy-mm-dd hh:mi:ss.mmm (24h) 126 Yyyy-mm-ddthh:mm:ss.mmm (no spaces) 130 DD Mon yyyy hh:mi:ss:mmmAM 131 Dd/mm/yy Hh:mi:ss:mmmAM Http://wenku.baidu.com/link?url=WOvPowJgmPWG61jLijDw4TsGRPw9bYRp67fgkN-ktIj1jtC_ W17l-5i-bbhjrgdfkqqigieerxgmmxezp2-8tcvckzp5eilhv07obez-cdk&qq-pf-to=pcqq.group Gets the number of days in the month: Select Day (DateAdd (Mm,1,getdate ())-day (GETDATE ())) Get the number of days in the month Select GETDATE ()--Current date Select Day (getdate ())--Current days Select GETDATE ()-day (GETDATE ())--Last day of last month select DATEADD (MM, 1,getdate ())-day (GETDATE ())--plus one months Select Day (DateAdd (Mm,1,getdate ())-day (GETDATE ()))--Get the number of days in the month SQL Server take date time part In this article, the date GetDate () is made up of two parts, namely today's date and time of day: Select GetDate () With Datename () you can get the corresponding year, month, day, and then connect them together: Select Datename (Year,getdate ()) + '-' +datename (Month,getdate ()) + '-' +datename (Day,getdate ()) In addition, Datename () can also get hours, times, seconds, days of the week, weeks, respectively, as follows: Select Datename (Hour,getdate ()) Select Datename (Minute,getdate ()) Select Datename (Second,getdate ()) Select Datename (Weekday,getdate ()) Select Datename (Week,getdate ()) Date type in SQL the default format for datetime is YYYY-MM-DD hh:mi:ss:mmm, but most of the time we just want to get his date part, not the later. The previous article mentions using the Datename () function to intercept a date that does not contain a time part, and now it is more simple to get to a date that does not contain time! Use the CONVERT () function: Select CONVERT (char), GetDate (), as Date * The 3rd parameter is used to set the display style of the date type data, the following describes the parameters of several styles: MM DD yyyy 101 mm/dd/yyyy 102 YYYY.MM.DD 103 dd/mm/yyyy 106 dd MM YYYY 108 Hh:mi:ss (Time) 111 YYYY/MM/DD Yyyymmdd Yyyy-mm-dd 1 SELECT CONVERT (varchar), GETDATE (), 0) 9/9:12am 2 SELECT CONVERT (varchar (), GETDATE (), 1) 05/09/11 3 SELECT CONVERT (varchar (), GETDATE (), 2) 11.05.09 4 SELECT CONVERT (varchar (), GETDATE (), 3) 09/05/11 5 SELECT CONVERT (varchar (), GETDATE (), 4) 09.05.11 6 SELECT CONVERT (varchar (), GETDATE (), 5) 09-05-11 7 SELECT CONVERT (varchar (+), GETDATE (), 6) 09 05 11 8 SELECT CONVERT (varchar (+), GETDATE (), 7) 05 09, 11 9 SELECT CONVERT (varchar (), GETDATE (), 8) 09:13:14 Ten SELECT CONVERT (varchar), GETDATE (), 9) 9 9:13:14:670am SELECT CONVERT (varchar), GETDATE (), 10) 05-09-11 SELECT CONVERT (varchar), GETDATE (), 11) 11/05/09 SELECT CONVERT (varchar), GETDATE (), 12) 110509 + SELECT CONVERT (varchar (+), GETDATE (), 13) 09 05 2011 09:13:14:670 SELECT CONVERT (varchar), GETDATE (), 14) 09:13:14:670 + SELECT CONVERT (varchar, GETDATE (), 20) 2011-05-09 09:13:14 21 SELECT CONVERT (varchar), GETDATE (), 2011-05-09 09:13:14.670 SELECT CONVERT (varchar), GETDATE (), 05/09/11 9:15:33 AM SELECT CONVERT (varchar, GETDATE (), 23) 2011-05-09 SELECT CONVERT (varchar), GETDATE (), 24) 09:15:33 + SELECT CONVERT (varchar (+), GETDATE (), 25) 2011-05-09 09:15:33.140 SELECT CONVERT (varchar), GETDATE (), 9 9:15AM SELECT CONVERT (varchar), GETDATE (), 101) 05/09/2011 SELECT CONVERT (varchar), GETDATE (), 102) 2011.05.09 SELECT CONVERT (varchar, GETDATE (), 103) 09/05/2011 104-SELECT CONVERT (varchar (+), GETDATE (), (+)) 09.05.2011 SELECT CONVERT (varchar, GETDATE (), 105) 09-05-2011 106 SELECT CONVERT (varchar (+), GETDATE (), 09 05 2011 SELECT CONVERT (varchar), GETDATE (), 107) 05 09, 2011 108-SELECT CONVERT (varchar (+), GETDATE (), (+) 09:16:38 -SELECT CONVERT (varchar), GETDATE (), 109) 9 9:16:38:543am SELECT CONVERT (varchar), GETDATE (), 110) 05-09-2011 GETDATE-SELECT CONVERT (varchar, 111) 2011/05/09 GETDATE-SELECT CONVERT (varchar, 112) 20110509 SELECT CONVERT (varchar), GETDATE (), 113) 09 05 2011 09:17:19:857 (varchar), GETDATE (), 114) 09:17:19:857 Panax Notoginseng SELECT CONVERT (varchar (+), GETDATE (), 120) 2011-05-09 09:17:19 SELECT CONVERT (varchar), GETDATE (), 121) 2011-05-09 09:17:19.857 All-in-a-SELECT CONVERT (varchar (+), GETDATE (), 126) 2011-05-09t09:17:19.857 6-SELECT CONVERT (varchar (), GETDATE (), $)????? ??????? 1432 9:17:19:857am GETDATE SELECT CONVERT (varchar (+), +/-(), 131) 6/06/1432 9:17:19:857am

SQL Server Date function