SQL Server Time Sinks

SQL Server DATE function

(1)getdate returns the date and time of the current system, accurate to 3 milliseconds

Requirements: Get the current time of the system

Statement:SELECT getdate ()

Execution Result:

Note:getutcdate (): used to return the current day and time of the server corresponding to the GMT, accurate to 3 milliseconds. He is very useful for companies that span time zones.

(2)dateadd Add or subtract a period of time on the date of enactment to return a new datetime

Requirement: add 2 days on 2008-10-12 Day

Statement:SELECT dateadd (day,2, ' 2008-10-12 ')

Execution Result:

If you want to get the time from now onwards , the syntax is as follows:

SELECT DateAdd (hh,100,getdate)

Note: If you subtract a period of time, use a negative value.

(3)DateDiff Returns the time between two dates

Requirements: Returns the time period between 2008-1-1 Day and 2009-3-5 Day

Statement:SELECT DateDiff (day, ' 2008-1-1 ', ' 2009-3-5 ')

Request:Mike couple 2001-5-4 married and ask them how many years have they been married? How many days have they been married?

Statement:Select
DateDiff (yy, ' 2001-5-4 ', GETDATE ()) as the year of marriage ,
DateDiff (DD, ' 2001-5-4 ', GETDATE ()) as wedding day

Note: The return value is the subsequent date minus the previous date, and theDateDiff () function does not calculate the entire date data, but instead calculates the date from which it was extracted, so the previous note is to calculate which part of the year, month, and day.

(4) DatePart Returns the specified individual part of the date or time, the integer value

Requirements: Return time 2009-5-6 Day of the month day

Statement:SELECT datepart (day, ' 2009-5-6 ')

Execution Result:

Note: If you want to return years or months , change Day to year or month

(5) Datename string that returns the specified part of the date

Requirements: Return 2009-5-6 of the week

Statement:SELECT datename (Weekday, ' 2009-5-6 ')

Execution Result:

You can specify the date part parameters and abbreviations returned as the following table.

Parameters	SQL Server abbreviation	Acess and asp abbreviations	Description
Year	Yy	yyyy	Years,1753-9999
Quarter	Qq	Q	Quarterly,1-4
Month	Mm	M	month,1-12
Day of the Year	Dy	Y	The number of days in a year,1-366
Day	Dd	D	Number of days in January,1-31
Weekday	Dw	W	The number of days in a week,1-7
Week	Wk	Ww	Week, week of the year,1-52
Hour	Hh	H	When the0-24
Minute	Mi	N	Points,0-59
Second	Ss	S	Seconds,0-59
Millisecond	Ms	-	MS,0-999

For example, returns the year of the current server time

Statement:

Select Datename (Yy,getdate ())

The result of the implementation is:

Note: get system Current time in acess and asp with date () or now (), other DateDiff datepart DateAdd And so on are also available, usage similar.

Note: Datename and the DatePart The function is basically the same, just a return is nvarchar , a return is Int.

SQL Server Time Sinks