SQL Server obtains the current system time

Getdate // get the current date of the system

Datepart // obtain the specified date part (year, month, day, And hour table)

Getdate () function: gets the current date and time of the system. The return value is of the datetime type.

Usage: getdate ()

Example:

Select getdate () as DTE, dateadd (day,-1, getdate () as nowdat

Output result:

DTE nowdat
------------------------------------------------------
19:13:10. 083 19:13:10. 083

(1 row (s) affected)

Datepart () function: returns the specified part of the time in integer format.

Usage: datepart (datepart, date)

Parameter description: the part of the time to be returned for datepart. values include year, month, day, hour, and minute.

Date is the specified time.

Example:

Select datepart (month, getdate () as 'month number'

Output result:

Month number

------------

11

(1 row (s) affected)

Dateadd () function: adds an integer to the specified part of the specified time to return a new time value.

Usage: dateadd (datepart, number, date)

Parameter description: datepart (same as above)

Date (same as above)

The value to be added by number. It is an integer that can be positive or negative. A positive value returns the time value after date, and a negative value returns the date.

Previous Time Value

Example:

Select getdate () as today

Select dateadd (day,-1, getdate ())

Select dateadd (day, 1, getdate ())

Output:

Today

---------------------------

19:42:41. 410

(1 row (s) affected)

Yesterday

---------------------------

2017-11-20 19:42:41. 410

(1 row (s) affected)

Tomorrow

---------------------------

19:42:41. 410

(1 row (s) affected)

Datediff () function: returns the difference between two times in the specified time range. Returns an integer. For example, between and

The difference is calculated as nine days. The difference is calculated as one year from to, and the difference is calculated as nine months from.

Usage: datediff (darepart, date1, date2)

Parameter description: datepart (same as above)

Date1 and date2 (same as date)

Example:

Select datediff (month, '2017-6-12 ', '2017-6-21') as

Output:

A

-----------

12

(1 row (s) affected)

SQL server2005 common functions and paging solutions:

Use time and date functions
Getdate (): Get the current system time
Dateadd (datepart, number, date): Calculate the new time value after adding a Time Value Based on a time value, such as: dateadd (YY, 30, getdate ())
Datediff (datepart, startdate, enddate): calculate the difference between the two times, for example, datediff (YY, getdate (), '2017-08-08 ')
Dataname (datepart, date): obtains the values of different parts of the time. The returned value is a string.
Datepart (datepart, date): similar to datename, but the return value is an integer.
Day (date): Get the number of days in the specified time
Month (date): gets the month of the specified time
Year (date): gets the year of the specified time

Question 1:
Table A is a table with a new record, and Field T is the insert time of the record. Now I want to know what is inserted into the table every 10 seconds.
Time and number of records with a total of more than N records are sorted in ascending order. Please write the SQL statement that completes this function.
Solution:
Declare @ t datetime
Select @ T = min (T) from B
Select max (T) as maxt, min (T) as mint, count (*) as num from B group
Datediff (SS, @ T, T)/10 having count (*)> 1 order by count (*) DESC

Global variables:
It starts with @ and cannot be customized by the user. Here are several common global variables:
@ Rowcount: indicates the number of rows affected by the last statement.
@ Error: the error message returned by the previous SQL statement.
@ Identity: for a table with an ID column, the system generates a new ID for each table. This variable records the recently generated ID.

Rowcount: a global variable at the session level. For example, set rowcount 3 sets the maximum number of returned records to be three. The function of this variable is similar
Top clause. The top clause applies to a single SELECT statement that specifies the clause. Set rowcount will remain valid until another
Set rowcount statement. For example, set rowcount 0 will disable this option.

Indetity_insert: Session-level global variable
At any time, the identity_insert attribute of only one table in a session can be set to on. If a table already has this attribute set
If it is set to on, SQL Server 2005 returns an error message when the set identity_insert on statement is sent to another table,
It indicates that set identity_insert has been set to on and reports that the table whose attribute is set to on has been set.
If the inserted value is greater than the current table id value, SQL Server automatically uses the new value as the current ID value.
Set identity_insert is set during execution or running, rather than during analysis.
DBCC checkident (table_name, reseed, n): This statement is used to correct the current ID value of a column.
Set the identifier to n. If the primary key or unique constraint exists in the ID column, invalid ID information will cause error message 2627.

Several paging solutions for SQL Server:
Solution 1:
Declare @ pageindex int, @ pagesize int, @ recordnum int
Set @ pageindex = 3
Set @ pagesize = 3
Select top (@ pagesize) * from grade where id not in (select top
(@ PageIndex-1) * @ pagesize) ID from grade) // display 7th to 9th

Select @ recordnum = count (*) from grade // display the total number of records

Comment: The efficiency is not high, and the retrieved data is sorted by ID. If you want to sort by other fields, you cannot.

Solution 2:
Declare @ ID int
-- Set rowcount 3
-- Select @ ID = ID from Grade
Select top 3 @ ID = ID from grade // This sentence is equivalent to the above two sentences
Select top 3 * Form grade where ID> @ ID // query 4th to 6th records

Comment: The efficiency is slightly higher than the solution, but the retrieved data is also sorted by ID. If you want to sort by other fields

Solution 3:
Create Table # TABLE (new_id int identity (1, 1) primary key, Id INT)
Insert into # TABLE (ID) Select ID from Grade
Select a. * from Grade A join # Table B on (B. new_id between 6 and 9) and A. ID = B. ID
// Retrieve 6th to 9th entries,
Comment: The principle is to insert the primary key of the table to be paged into the temporary table. The field of the temporary table is an identification column and
The primary key column of the table on the page (including multiple primary keys, the above two methods will not work ). It is characterized by high efficiency and Retrieval
Data can be sorted according to the required fields.

Temporary table:
Solution 3 is to use a temporary table, which is similar to a permanent table, but the temporary table is stored in tempdb.
It is automatically deleted when used again.
Temporary tables can be local or global. They differ in terms of name, visibility, and availability. Local temporary table
With a single digit sign (#). They are only visible to the current user connection.
The instance is deleted when it is disconnected. The name of the global temporary table starts with two numerical symbols (#).
All are visible. All users that reference this table are deleted when they are disconnected from SQL Server.

Table-level variables:
Table-level variables can also be used in the example of pagination above, as shown below:

Declare @ Table (newid int identiey (1, 1), Id INT)
Insert into @ table (ID) Select ID from Grade
Select a. * from Grade A join @ table B on (B. new_id between 6 and 9) and A. ID = B. ID
This method is more efficient than using temporary tables for paging.

String functions:
Left (stringexp, intexp): number of characters on the left of the string
Len (strexp): calculates the length of a specified string.
Char (intexp): returns the corresponding character based on the ASCII code of the specified character.
ASCII (strexp): converts a character into an ascii code.
Lower (strexp): converts a string to lowercase.
Upper (strexp): converts a string to uppercase.
Ltrim (strexp): removes the space on the left of the string.
Rtrim (strexp): remove the space on the right of the string
Substring (strexp, intexp, intexp): captures the substring of a string based on the specified index.
Replace (strexp, stroldstr, strnewstr): replace some content in the string with a new string.

Other System conversion functions:
Isnull (logical expression instead of value): determines whether the logical expression is null. If yes, It is replaced by the specified value.
Convert () and cast:
Cast and convert functions can also be used to obtain various special data formats, and can be used to select a list, where
Clause and any position where expressions are allowed. If you want the transact-SQL program code to comply with the SQL-92,
Use cast instead of convert. If you want to use the functions in convert, use convert
Instead of using cast.
When using cast or convert, you must provide the following information:
Expression to be converted. For example, a sales report requires that the sales data be converted from currency data to character data.
The data type to convert the specified expression to, for example, varchar or other SQL Server System data types.
Unless the converted value is stored, the conversion is only valid for the time range of Cast function or convert function.
If the length of the data type is not specified during conversion, SQL Server automatically uses 30 as the length value.

Problem:
There is a commodity table with three fields: purchase date, purchaser, and price. Statistics on each purchaser and each
The total consumption price of the quarter, and calculate the subtotal (that is, the total consumption price of the year)

Solution:

Create a shop table:
Create Table shop
(
Selldate datetime default (getdate ()),
Name varchar (20 ),
Price money,
)
Add some test data:
Insert into shop (selldate, name, price) values ('1970-05-12 ', 'zhang san', 75)
Insert into shop (selldate, name, price) values ('2017-07-12 ', 'zhang san', 50)
Insert into shop (selldate, name, price) values ('2017-09-12 ', 'zhang san', 43)
Insert into shop (selldate, name, price) values ('2017-11-12 ', 'l4', 67)
Insert into shop (selldate, name, price) values ('2017-01-12 ', 'l4', 98)
Insert into shop (selldate, name, price) values ('2017-06-12 ', 'l4', 2006)
Insert into shop (selldate, name, price) values ('2017-08-12 ', 'aliyun', 2006)
Insert into shop (selldate, name, price) values ('2017-04-12 ', 'aliyun', 2006)
Insert into shop (selldate, name, price) values ('2017-10-12 ', 'aliyun', 85)
Insert into shop (selldate, name, price) values ('1970-08-12 ', 'zhao liu', 11)
Insert into shop (selldate, name, price) values ('1970-12-12 ', 'zhao liu', 2006)
Insert into shop (selldate, name, price) values ('1970-02-12 ', 'zhao liu', 17)
Insert into shop (selldate, name, price) values ('1970-01-12 ', 'zhao liu', 2006)

Select * from shop

Select statement to solve the problem:
Select name,
Sum (case when datepart (Q, selldate) = 1 then price else 0 end) first quarter,
Sum (case when datepart (Q, selldate) = 2 then price else 0 end) quarter 2,
Sum (case when datepart (Q, selldate) = 3 then price else 0 end) Quarter 3,
Sum (case when datepart (Q, selldate) = 4 then price else 0 end) in the fourth quarter,
Sum (price) Subtotal
From shop group by name

==========================================
Time Conversion Problems

The database segment has a time field and its type is date/time. I have the following SQL statement to insert the current system time:
Strsql = "insert into BBS (title, name, text, [time]) values (" + textbox1.text + "," + textbox2.text + "," + textbox3.text + ", datename (year, getdate ();))";

A time conversion error is always prompted. What is the time conversion parameter and how to write this statement!

No. 1 Author: lansluo

Use the getdate () method to obtain the current system time.

No. 2 by lansluo

Strsql = "insert into BBS (title, name, text, [time]) values (" + textbox1.text + "," + textbox2.text + "," + textbox3.text + ", getdate ())";

In this way, you can.

Author: ddangerous169

Why do you need to add quotation marks? Use it directly.
Strsql = "insert into BBS (title, name, text, [time]) values (" + textbox1.text + "," + textbox2.text + "," + textbox3.text + ", getdate) ";

Author: vzxq

String news_date = datetime. Now. tow.datestring ();

Author: thinkingforever

Strsql = "insert into BBS (title, name, text, [time]) values (" + textbox1.text + "," + textbox2.text + "," + textbox3.text + ", year (getdate ()))";

[Reprinted from http://hi.baidu.com/kaisep/blog/item/4c70dcce49ae540592457e82.html 3q]