Date and time functions in SQL Server _mssql

Date and time functions in SQL Server

1. Current system date, time
Select GETDATE ()

2. DateAdd returns a new datetime value on the basis of adding a period of time to the specified date

For example: Add 2 days to date
Select DATEADD (day,2, ' 2004-10-15 ')--back: 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

4. DatePart returns an integer representing the specified date part 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 the week =datename (weekday, ' 2004-10-15 ')

Second, SQL date format conversion

Select CONVERT (varchar, GETDATE (), 120)
2004-09-12 11:06:08
Select replace (replace (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 I do not use the date format conversion method:

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 i.e. 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 i.e. 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
Select and WHERE clauses of the SELECT statement and the use of date functions in expressions. The following methods are used:

A date function argument in which the number of parameters varies by 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 value of the month in Date_expression.
Unlike the day () function, the MONTH () function, when the argument is an integer, returns an integer value of 1, which is what SQL Server considers its
It's January 1900.

· Year ()

The year () function syntax is as follows:
Year (<date_expression>)
The year () function returns the value of years in Date_expression.
Reminder: When using a date function, the date value should be between 1753 and 9999, which is the date that the SQL Server system recognizes
Wai, otherwise there will be errors.

· DATEADD ()

The DATEADD () function syntax is as follows:
DATEADD (&LT;DATEPART&GT;, <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
"is often used in date functions to specify the components that make up 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 () (&LT;DATEPART&GT;, <date1> <date2>)
The DATEDIFF () function returns the difference between the datepart of two specified dates, that is, the date2 exceeds the date1 gap value, and its
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 allowed by the DATEDIFF () function is 68: Year DatePart is
Millisecond, the maximum gap allowed by the DATEDIFF () function is 24 days, 20 hours, 30 minutes, 23 seconds, and 647 milliseconds.

· Datename ()

The Datename () function syntax is as follows:
Datename (<datepart&gt, <date) >
The Datename () function returns the specified portion of a date in the form of a string. specified by DatePart.

· DATEPART ()

The DATEPART () function syntax is as follows:
DATEPART (&LT;DATEPART&GT; <date>)
The DATEPART () function returns the specified portion of a date as an integer value. This section is specified by DatePart.
DATEPART (dd, date) is equivalent to day (date)
DATEPART (mm, date) is equivalent to 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.

In the development of database applications, often encounter problems of processing time, such as query the specified time records. Here are some of the common questions
, combined with some of their own experience, and you discuss this kind of problem.

Let's begin by introducing the use of several main functions of processing time in SQL Server:

GETDATE () function: Gets the current date and time of the system. Returns the value of the datetime type.

Usage: getdate ()
Example:

Select GETDATE () as Dte,dateadd (Day,-1,getdate ()) as Nowdat
Output results:
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 portion of a time as an integer.
Usage: DATEPART (datepart,date)
Parameter description: DatePart to return the part of the time, commonly used to value year, month, day, hour, minute.
Date is the specified time.
Example:
SELECT DATEPART (month, GETDATE ()) as ' month number '
Output results:
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 increase the value of the integer, can be positive negative, positive values return the time after the date value, negative returns date
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 the difference that is calculated in the specified time portion of two time. Returns an integer value. such as 1991-6-12 and 1991-6-21
Between the day
To calculate the difference between 9 days, 1998-6-12 and 1999-6-23 by year, the difference is 1 years, 1999-12-1 and 1999-3-12 monthly difference of 9 months
Usage: DateDiff (DAREPART,DATE1,DATE2)
Parameter description: DatePart (IBID.)
Date1, Date2 (ditto date)
Example:
Select DateDiff (Month, ' 1991-6-12 ', ' 1992-6-21 ') as a

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, as specified in interval date2-date1
DATEADD (Interval,number,date)	In the manner specified in interval, plus the date after number
DatePart (Interval,date)	Returns the integer value of the specified part in date interval
Datename (Interval,date)	Returns the string name of the specified section in date interval

The set values for the parameter interval are as follows:

Value	Abbreviation (SQL Server)	Access and ASP	Description
Year	Yy	yyyy	Year 1753 ~ 9999
Quarter	Qq	Q	Quarter 1 ~ 4
Month	Mm	M	Month 1 ~ 12
Day of the Year	Dy	Y	The number of days in a year, the first day of the year 1-366
Day	Dd	D	Day, 1-31
Weekday	Dw	W	The number of days in a week, the day ordinal of a week, 1-7
Week	Wk	Ww	Weeks, the first weeks of the year 0 ~ 51
Hour	Hh	H	Time 0 ~ 23
Minute	Mi	N	Minutes 0 ~ 59
Second	Ss	S	seconds 0 ~ 59
millisecond	ms	-	milliseconds 0 ~ 999