SQL advanced (7) Date function, sqldate Function

Source: Internet
Author: User

SQL advanced (7) Date function, sqldate Function
SQL advanced (7) Date function SQL Date when we process a Date, the most difficult task is to ensure that the format of the inserted Date matches the format of the Date column in the database.
As long as the data contains only the date part, running the query will not cause problems. However, the time is complicated.
Before discussing the complexity of date queries, let's take a look at the most important built-in date processing functions.
The following table lists the most important built-in Date functions in SQL Server:

Function Description
GETDATE () Returns the current date and time.
DATEPART () Returns a separate portion of the date/time.
DATEADD () Add or subtract a specified time interval from a date
DATEDIFF () Returns the time between two dates.
CONVERT () Display date/time in different formats
SQL Date data type SQL Server uses the following data types to store Date or Date/time values in the database:
  • DATE-format YYYY-MM-DD
  • DATETIME-format: YYYY-MM-DD HH: MM: SS
  • SMALLDATETIME-format: YYYY-MM-DD HH: MM: SS
  • TIMESTAMP-format: unique number
If SQL Date Processing does not involve time, we can easily compare two dates!
Suppose we have the following "Orders" table:
OrderId ProductName OrderDate
1 Computer 2008-12-26
2 Printer 2008-12-26
3 Electrograph 2008-11-12
4 Telephone 2008-10-
Now, we want to select the record whose OrderDate is "" from the preceding table.
We use the following SELECT statement:
SELECT * FROM Orders WHERE OrderDate='2008-12-26'
Result set:
OrderId ProductName OrderDate
1 Computer 2008-12-26
3 Electrograph 2008-12-26
Assume that "Orders" is similar to this (note the time section in the "OrderDate" column ):
OrderId ProductName OrderDate
1 Computer 16:23:55
2 Printer 10:45:26
3 Electrograph 14:12:08
4 Telephone 2008-10-19 12:56:10
If we use the preceding SELECT statement:
SELECT * FROM Orders WHERE OrderDate='2008-12-26'
Then we cannot get the result. This is because the query does not include the date with time.
Tip: If you want to make the query simple and easier to maintain, do not use the time part in the date!
SQL function problems? Getdate ()?

Directly use the function:
Dateadd (hour, 6, getdate () -- add six hours
Dateadd (hour,-6, getdate () -- minus six hours

Insert the date function in an SQL statement

SQL = "insert into inlib (inDate, bookname, bookid, inNum, price, money) values (getdate (), '" & bookname & "'," & bookid &", "& inNum &", "& price &", "& money &")"

InNum, and price are separated by commas (,).

No.
Check whether the data type is incorrect.
Or place the SQL statement in the query analyzer for execution.
See what is prompted

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

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.