Date function in SQL database---2017-04-12

One, SQL Server time Date function detailed

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 ')

Second, 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 value of parameter interval is 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.

Instance:

1. Get the average between two dates

Select DATEADD (Dd,datediff (Day, ' 2001-08-12 ', ' 2011-06-25 ')/2, ' 2001-08-12 ')

Results: 2006-07-19 00:00:00.000

Original address: http://blog.csdn.net/daiyue945/article/details/6781466

Date function in SQL database---2017-04-12