SQL Server Date-time functions

Source: Internet
Author: User
Tags getdate

Inter-function

  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:

-- output 2013-03-09 15:16:00.570

2. getUTCDate get UTC time Value

-- 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:

Year (-- output 

  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:

Month (-- 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:

Day (-- 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, 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.

Datename (Quarter,-- Output 2 (June) 

Example:

DatePart (Quarter,-- Output 1 now is 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:

  GETDATE ()    -- output 2013-03-09 16:03:59.293 dateadd (YYYY,Ten,--      

  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:

  DateDiff (YYYY,'2011-11-11','2012-12-12 '--the output is 1 DateDiff after the 1-year subtraction (Day,' 2011-11-11','2012-12-12-- outputs 3,972 days of difference between dates        

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

DateDiff (Day,rq,-- where Rq is the date column, what does this line of 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

-- 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 ();

--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.

Sysdatetimeoffset ()-    - 2013-06-18 16:40:34.4322865 +08:00 '+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:

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

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

SQL Server Date-time functions

Related Article

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.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.