SQL Server Date-time functions

Date-time functions

　　1. Get the current date getdate

The GETDATE () function returns the date and time of the computer on which the current SQL Server servers are located, in the format of the datetime data type. Its syntax format is GETDATE (). The return value is rounded to the nearest fractional fraction of the second, with a precision of. 333 seconds the database 17 offset is not included.

Example:

Select GETDATE ()--Output 2013-03-09 15:16:00.570

2. getUTCDate get UTC time Value

Select getUTCDate ()--2013-06-18 08:02:53.253

　　This gets the UTC time.

3. Get Annual Information year　　

The year function returns annual information for a specific date in the format of the int data type. Its syntax format is year (date data). One of the date data is an expression that resolves to a time, date, smalldatetime, DateTime, datetime2, or DateTimeOffset value, a column expression, a user-defined variable, or a string literal.

Example:

Select year (GETDATE ())--Output 2013

　　4. Get month information

The month function returns the monthly information for a specific date in the format of the int data type. Its syntax format is month (date data). One of the date data is an expression that resolves to a time, date, smalldatetime, DateTime, datetime2, or DateTimeOffset value, a column expression, a user-defined variable, or a string literal.

Example:

Select Month (GETDATE ())--Output 3

　　5. Get Days Information Day

The day function returns the number of days information for a specific date in the format of the int data type. Its syntax format is day (date data). One of the date data is an expression that resolves to a time, date, smalldatetime, DateTime, datetime2, or DateTimeOffset value, a column expression, a user-defined variable, or a string literal.

Example:

Select Day (getdate ())--Output 9

　　6, get any time part datepart

The DATEPART function returns the specified portion of a datetime data in the format of the int data type.

1. Syntax format:

DATEPART (datepart, date data)

DatePart: The format code for the specific data to be returned.

Date data: An expression that resolves to a time, date, smalldatetime, DateTime, datetime2, or DateTimeOffset value, a list expression, a user-defined variable, or a string literal.

2, DatePart value

The value of the DatePart section is shown in the following table:

Description	Take value
Return Annual Information	Year, YYYY, YY
Return month information	Month, MM, M
Return date information	Day, DD, D
Return Week information	Week, WK, WW
Returns the weekly information of the week	WeekDay, DW
Return Quarterly information	Quarter, QQ, Q
Returns information for the day of the year	DayOfYear, DY, Y
Return hour information	Hour, HH
Return minute information	Minute, MI, N
Returns the second message	Second, SS, SS
Returns the millisecond information	Millsecond, MS

7, get any time part datename

Returns a String that represents the specified date portion of the specified date.

SELECT Datename (Quarter,getdate ())--Output 2 (June)

Example:

Select DATEPART (quarter,getdate ())--Output 1 is now the quarter

　　8. Date addition DateAdd

The DateAdd function returns the value of a datetime data type after a date is added to a specific time interval value.

1. Syntax format:

DATEADD (datepart,numer,date data)

Numer: The value to add to the Detepart. If a non-integer value is specified, the decimal part of the value is discarded.

Date data: An expression that resolves to a time, date, smalldatetime, DateTime, datetime2, or DateTimeOffset value, a column expression, a user-defined variable, or a string literal.

DatePart: Time section. The previous section.

Example:

Select GETDATE ()    --Output 2013-03-09 16:03:59.293 select DateAdd (yyyy,10,getdate ())    --Added ten years    output 2023-03-09 16:04:36.893        

　　8. Date Subtraction DateDiff

The DateDiff function subtracts two specific date data, resulting in the return of specific data in the format specified by Detepart.

1. Syntax format:

DateDiff (Datepart,startdate,enddate)

The execution will use EndDate minus the StartDate value if startdate later than EndDate will return negative values. Execution results if the integer range is exceeded, an error is indicated.

StartDate: An expression that resolves to a time, date, Smalldate, DateTime, datetime2, or DateTimeOffset value, a column expression, a user-defined variable, or a string literal.

EndDate: An expression that resolves to a time, date, Smalldate, DateTime, datetime2, or DateTimeOffset value, a column expression, a user-defined variable, or a string literal.

DatePart: Time section.

Example:

Select DateDiff (YYYY, ' 2011-11-11 ', ' 2012-12-12 ')    --Output 1 years after subtracting is indeed 1 Select DateDiff (Day, ' 2011-11-11 ', ' 2012-12-12 ') )     --outputs 3,972 days of difference in date

2013-3-13 supplement, use this method to query the current record:

SELECT * FROM Test where DATEDIFF (Day,rq,getdate ()) = 0-where Rq is the date column, what does this line code mean? GETDATE () Subtract the value of the Rq column from the value of the 0,rq column minus today, with a number of 0

　　9. New date-time function in SQL Server 2008

　　　　1. Get the system time Sysdatetime

The Sysdatetime function returns the date and time of the current system in the format of the datetime2 (7) data type. Its syntax format is Sysdatetime (). The return value has seven decimal digits, but is only accurate to 10 milliseconds. The database offset is not included.

SELECT sysdatetime ()--2013-06-17 22:17:59.0610211

　　　　2. Get the current date and time Sysdatetimeoffset

The Sysdatetimeoffset function returns the date and time of the current system in the format of the DateTimeOffset (7) data type. Its syntax format is Sysdatetimeoffset (). The return value has seven decimal digits, but is only accurate to 10 milliseconds. The database time zone offset is included.

SELECT sysdatetimeoffset ()--2013-06-17 22:18:30.1788009 +08:00

　　　　3. Get the system UTC time Sysutcdatetime

Select Sysutcdatetime ()--2013-06-18 08:09:17.8229505

　　10. Current_timestamp Current Database system timestamp

　　Returns the current database system timestamp, the type of the return value is datetime, and does not include the database time zone offset. This is worth the operating system of the computer running the instance of SQL Server. Equivalent to GETDATE ();

SELECT Current_timestamp--2013-06-18 16:16:10.007

　　11. Switchoffset Change the time offset

SwitchOffset changes the time zone offset of the DateTimeOffset value and retains the UTC value.

Select Sysdatetimeoffset ()    --2013-06-18 16:40:34.4322865 +08:00 SELECT switchoffset (Sysdatetimeoffset (), ' +07:00 ')--2013-06-18 15:40:53.5403794 +07:00

　　12, Todatetimeoffset datetime2 turn DateTimeOffset

Returns a DateTimeOffset value that is converted from a datetime2 expression .

Select Sysdatetimeoffset ()-    -2013-06-18 16:40:34.4322865 +08:00 Select Todatetimeoffset (GetDate (), ' +07:00 ')-- 2013-06-18 16:46:10.537 +07:00

Notice that the code above changes the time zone only, but does not change the value.

　　Sysdatetime and sysutcdate are higher than GetDate and getutcdate in fractional seconds. The Sysdatetimeoffset contains the system time zone offset. Sysdatetime, Sysutcdate, and Sysdatetimeoffset can be assigned to variables that take any date and time type.

　　13, judge whether the date data isdate

The IsDate function can verify that an input value is a valid date data

1. Grammatical structure:

ISDATE (expression)

2. Return value

Returns 1 if the expression is a valid date, otherwise returns 0.

Example:

Select IsDate (' 2012-12-12 ')    -Output 1 Select isdate (' xxxx-12-12 ')--output 0

Http://technet.microsoft.com/zh-cn/library/ms186724.aspx#ModifyDateandTimeValues

SQL Server Date-time functions