Shulou(Shulou.com)05/31 Report--

What are the commonly used date and time functions in SQL Server? aiming at this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

1. DATEADD (datepart,number,date)

Function: returns the new datetime value after adding a time interval to the specified date

Parameter description:

Datepart: specifies which part of the date to increment the value.

Common values are: year (yy/yyyy), quarter (qq/q), month (mm/m), day (day/dd/d), week (wk/ww), hour (hh), minute (mi,n), second (ss/s).

Number: specifies the number to increase. If it is a non-integer, the decimal part will be dropped. A negative number subtracts the corresponding interval.

Date: indicates the date to be processed.

Usage:

-- add three months select DATEADD (mm,3,'2020-12-30') to the specified date;-- subtract one day from the specified date (select DATEADD (day,-1,'2020-12-30')

2. DATEDIFF (datepart,startdate,enddate)

Function: specify the number of times for the difference between two times. Such as days, hours, minutes and so on.

Parameter description:

Datepart: specifies the returned date-time dimension (days, hours, minutes).

Common values are: year (yy/yyyy), quarter (qq/q), month (mm/m), day (day/dd/d), week (wk/ww), hour (hh), minute (mi,n), second (ss/s).

Startdate: it's understandable to be subtracted.

Enddate: you can understand the subtraction. Returns a negative value if startdate is greater than enddate.

Usage:

SELECT DATEDIFF (day,'2021-02-05) AS DiffDate-return 4 days

3. DATENAME (deatpart,date)

Function: returns a string of the specified part of the specified date.

Parameter description:

Datepart: specifies the returned date-time dimension (days, hours, minutes).

Common values are: year (yy/yyyy), quarter (qq/q), month (mm/m), day (day/dd/d), week (wk/ww), hour (hh), minute (mi,n), second (ss/s).

Date: date expression

Usage:

Week of DateName (quarter,GetDate ()) as' quarter 'DateName (week,GetDate ()) as' week of the year 'DateName (DAYOFYEAR,GetDate ()) as' Day of the year 'DateName (year,GetDate ()) as' year 'DateName (month,GetDate ()) as' month 'DateName (day,GetDate ()) as' day 'DateName (hour,GetDate () as') DateName (minute,GetDate ()) as' minutes' DateName (second,GetDate ()) as' seconds

4. DATEPART (deatpart,date)

Returns the integer for the specified part of the specified date. The usage is similar to DATENAME, and the return value is an integer. For example:

SELECT DATEPART (weekday,'20210209')-return value is 2 SELECT DateName (weekday,'20210209')-return value is Tuesday

5. DAY | MONTH | YEAR function

Function: returns the part of the day / month / year in which the specified date is located. You can replace it with DATEPART.

Usage:

SELECT DAY ('2021-02-09')-return 9 SELECT MONTH ('2021-02-09')-return 2 SELECT YEAR ('2021-02-09')-return 2021

6. EOMONTH (input [, month_to_add])

What it does: this function was introduced in SQLServer 2012. The EOMONTH function returns the date of the last day of a month before or after the specified date.

Syntax structure: = EOMONTH (start date, month before and after the specified start date)

Usage:

DECLARE @ date DATETIME = '2021-02-09; SELECT EOMONTH (@ date,7) AS Result;-plus seven months end date 2021-09-30 SELECT EOMONTH (@ date,-2) AS Result;-minus two months end date 2020-12-31

7 、 FROMPARTS

Function: generate a date value according to the input year, month and day parameters, and return.

Format: DATEFROMPARTS (year,month,day)

Parameter description:

Year: enter an integer value for the year

Month: enter the number of months between 1 and 12

Day: enter the day integer value corresponding to the year and month

Note: if any of the above three parameters is entered into null, null will be returned.

Usage:

SELECT DATEFROMPARTS (20202.009) AS [generate date data];-- 2020-02-09

8. GETDATE ()

This is all too common to get the current time of the system.

SELECT GETDATE ()-- returns the result 2021-02-09 08 2811.307

9. GETUTCDATE ()

Returns the current UTC time. Domestic use minus eight hours.

SELECT GETUTCDATE ()-- return result 2021-02-09 00 29 21.710 this is the answer to the question about which date and time functions are commonly used in SQL Server. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.