SQL Basic Operations--date function

Source: Internet
Author: User
Tags getdate time and date

SQL Date: when we work on dates, the hardest task is to ensure that the date you insert is formatted to match the format of the date column in the database. As long as the data contains only the date parts, running the query will not be a problem. However, if time is involved, the situation is a little more complicated. Before we discuss the complexity of the date query, let's look at the most important built-in date processing functions.

The most important built-in date functions in SQL Server: GETDATE (),DATEPART (),DATEADD (),DATEDIFF (),CONVERT ()

1. The GETDATE () function returns the current time and date from SQL Server.

--Basic UseSELECT GETDATE() asCurrentdatetime--Create an Orders table with a datetime column (OrderDate)CREATE TABLEOrders (OrderIdint  not NULL PRIMARY KEY, ProductNamevarchar( -) not NULL, OrderDatedatetime  not NULL DEFAULT GETDATE())

Note that OrderDate GETDATE () as the default value. The result is that when you insert a new row in the table, the current date and time are automatically inserted into the column. Now, we want to insert a record in the Orders table:

INSERT  into VALUES ('computer')

Results:

OrderId ProductName OrderDate
1 Computer 2008-12-29 16:25:46.635

2. The DATEPART () function returns a separate part of the date/time, such as year, month, day, hour, minute, and so on.

Grammar:

DATEPART (datepart, date)

The date parameter is a valid day expression. The datepart parameter can be the following value:

Suppose we have the following "Orders" table:

OrderId ProductName OrderDate
1 Computer 2008-12-29 16:25:46.635

We use the following SELECT statement:

SELECT DATEPART  as OrderYear, DATEPART  as OrderMonth, DATEPART  as Orderday  from Orders WHERE OrderId=1

Results:

OrderYear OrderMonth Orderday
2008 12 29

3. The DATEADD () function adds or subtracts the specified time interval from the date.

Grammar:

DATEADD (datepart,number, date)

The date parameter is a valid day expression. The number is the number of intervals you want to add, and for the future time, this is positive, and for the past time, this number is negative. The datepart parameter can be a value in.

We use the "Orders" table above. Now, we want to add 2 days to "OrderDate" so we can find the payment date. We use the following SELECT statement:

SELECT OrderId,DATEADD(Day,2 as orderpaydate from Orders

Results:

OrderId Orderpaydate
1 2008-12-31 16:25:46.635

4. The DATEDIFF () function returns a time between two dates.

Grammar:

DATEDIFF (datepart, startdate,enddate)

The StartDate and EndDate parameters are valid date expressions. The datepart parameter can be a value in. Use the following SELECT statement:

SELECT DATEDIFF (HH,'2008-12-29','2008-12-30' as Diffdate

Results:

Diffdate
24

5. The convert () function is a general function that converts a date to a new data type. Specifically, you can look at an article

Reference: SQL Date function

SQL Basic Operations--date function

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.