SQL Server System time

GETDATE () function: Gets the current date and time of the system. The return value is of type DateTime.

Usage: getdate ()

Example:

Select GETDATE () as Dte,dateadd (Day,-1,getdate ()) as Nowdat

DatePart () Function: Returns the specified part of the time as an integer.

Usage: DATEPART (datepart,date)

Parameter description: The part of the time to return when datepart, used to value year , month, day, hour, minute, second

The date is the specified time.

Example:

Select DATEPART (month, GETDATE ()) as ' month number '

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 increment, integer, positive negative, positive value to return time after date, negative value to return time before date

Example:

Select GETDATE () as Today

Select DATEADD (Day,-1,getdate ())

Select DATEADD (Day,1,getdate ())

DateDiff () Function: Returns a difference of two time to the specified time portion. Returns an integer value.

such as 1991-6-12 and 1991-6-21 between the days to calculate the difference of 9 days, 1998-6-12 and 1999-6-23 by year difference of 1 years, 1999-12-1 and 1999-3-12 by month difference of 9 months

Usage: DateDiff (DAREPART,DATE1,DATE2)

Parameter description: DatePart (IBID.)

Date1, Date2 (ibid. date)

Example:

Select DateDiff (Month, ' 1991-6-12 ', ' 1992-6-21 ') as a

Common time Functions:

Functions that use time and date

GETDATE (): Gets the current time of the system
DATEADD (datepart,number,date): Calculates a new time value after a time based on a time, such as: DateAdd (Yy,30,getdate ())
DateDiff (datepart,startdate,enddate): Calculates the difference of two time, such as: DateDiff (Yy,getdate (), ' 2008-08-08 ')
Dataname (datepart,date): Gets the value of a different part of time, the return value is a string
DatePart (datepart,date): Similar to Datename, except that the return value is integer type
Day: Gets the number of days of the specified time
Month (date): Gets the month of the specified time
Year (date): Gets the years of the specified time

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)//show 7th to 9th

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

Reviews: Inefficient, and the data taken out are sorted by ID. Not if you want to sort by another field.

Solution Two:
DECLARE @id int
--set ROWCOUNT 3
--select @id =id from Grade
Select Top 3 @id =id from grade//This sentence is equivalent to the two sentences
Select Top 3 * form Grade where id> @id//Check out 4th to 6th Records

Reviews: The efficiency is slightly higher than the scheme, but the data taken out is also sorted by ID. Not if you want to sort by another field

Solution Three:
CREATE TABLE #table (new_id int identity (primary) Key,id int)
INSERT into #table (ID) select ID from grade
Select A.* from Grade A joins #table B on (b.new_id between 6 and 9) and A.id=b.id
Take out 6th to 9th,
Comments: 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 identity column and to be divided
The primary key column of a page's table (including the case of multiple primary keys, which is not possible on either of these methods). Characterized by high efficiency, removing
The data can be sorted according to the field you want.

Temp table:
The solution above is to use a temporary table similar to a permanent table, but temporary tables are stored in tempdb when not
It is automatically deleted when you use it again.
There are two types of temporal tables: local and global. They differ in name, visibility, and usability. Local Temp Table
Names begin with a single number sign (#); they are only visible to the current user connection; When the user from the SQL Server
Deleted when the instance is disconnected. The name of the global temporary table begins with a two number sign (# #) and is created for any user
are visible when all users referencing the table are removed from SQL Server when they are disconnected.

Table-level variables:
The table-level variables can also be used for examples like the above pagination:

Declare @table table (newid int identiey (), id int)
INSERT into @table (ID) select ID from grade
Select A.* from Grade A joins @table B on (b.new_id between 6 and 9) and A.id=b.id
This approach is more efficient than using temporary table paging.

SQL Server System time