SQL Server functions full solution < four > date and Time Functions

Original: SQL Server function Full solution < four > date and Time Functions

Date and time functions are mainly used to deal with date and time values, this article mainly introduces the functions and usages of various date and time functions, the general Date function can use parameters of the DateTime type in addition to the parameters of the dates type, but ignores the time portion of the values. The same, a function that takes a time type value as a parameter, can accept parameters of the DateTime type, but ignores the date part.

1. Get the system current date function getdate ();

The getDate () function returns the date and time of the current database system, and the type of the return value is DateTime.
"Example" select GetDate () as currenttime;

2. Return the UTC date Function utcdate ()

The Utcdate () function returns the current UTC (World standard Time) Date value.
"Example" select getUTCDate () as Utctime;

Because I am deep within the jurisdiction of the Court, located in the East eight time zone, so the current time of the system is 8 hours ahead of UTC, so the UTC time shown here needs to subtract 8 hours of slack.

3. Function Day (d) to get the number of days

The day (d) function is used to return the specified date of D, which is the days of the one month, ranging from 1~31, which is functionally equivalent to DATEPART (dd,d).
"Example" Select Day (' 2015-04-30 01:01:01 ');

4. Get the month's function month (d)

The month (d) function returns an integer value for the month in the specified date d.
"Example" Select MONTH (' 2015-04-30 ') as Monthvalue;

5. Get the function of the year (d)

The year (d) function returns the integer value of the years in the specified date d.
"Example" Select year (' 2015-04-30 '), year (1997-07-01);

6. Get the function datename (dp,d) for the specified partial string value in the date

datename (dp,d) Returns the value of the corresponding part of the date according to the DP, for example, the year value in the date returned in month, month value in the date returned by the DP, and the other values that can be taken by the DPS are: Quater,dayofyear,day,week, Weekday,hour,minute,second and so on.
"Example" Select Datename (Year, ' 2015-04-30 01:01:01 ') as Yearvalue;
SELECT datename (quater, ' 2015-04-30 01:01:01 ') as Quatervalue;
SELECT datename (dayofyear, ' 2015-04-30 01:01:01 ') as Dayofyearvalue;
SELECT datename (day, ' 2015-04-30 01:01:01 ') as Dayvalue;
SELECT datename (week, ' 2015-04-30 01:01:01 ') as Weekvalue;
SELECT Datename (Weekday, ' 2015-04-30 01:01:01 ') as Weekdayvalue;
SELECT datename (hour, ' 2015-04-30 01:01:01 ') as Hourvalue;
SELECT datename (minute, ' 2015-04-30 01:01:01 ') as Minutevalue;
SELECT datename (second, ' 2015-04-30 01:01:01 ') as SecondValue;

7. Get the integer value of the specified part of the DATE function datepart (dp,d)

The DATEPART (dp,d) function returns the integer value of the corresponding part of the specified date, and the DP value is the same as the DateTime function.
"Example" Select DATEPART (Year, ' 2015-04-30 01:01:01 '), DATEPART (month, ' 2015-04-30 01:01:01 '),
DATEPART (DayOfYear, ' 2015-04-30 01:01:01 ');

8. Calculating the date and time of the function DateAdd (dp,num,d)

The DATEADD (dp,num,d) function is used to perform a date addition, returning the specified date value plus a new date after a time period. A partial value of the addition operation in the specified date of the DP, for example: Year,month,day,hour,minute,second,millsecond, num Specifies the value that is added to the DP, and if the value is a non-integer value, the fractional part of the value is discarded, D is the date on which the addition operation was performed.
SELECT DATEADD (year,1, ' 2015-04-30 01:01:01 ') as Yearadd,
DATETIME (month, 2, ' 2015-04-30 01:01:01 ') as Weekdayadd,
DATEADD (hour,2, ' 2015-04-30 01:01:01 ') as Houradd;

The script for the date function above example:

-Time FunctionSelect  getDate() ascurrenttime;SelectgetUTCDate () asUtctime;Select   Day('2015-04-30 01:01:01');SELECT  MONTH('2015-04-30') asMonthvalue;SELECT   Year('2015-04-30'), Year('1997-07-01');SELECT Datename( Year,'2015-04-30 01:01:01') asYearvalue,Datename(Quarter,'2015-04-30 01:01:01') asQuatervalue,Datename(DayOfYear,'2015-04-30 01:01:01') asDayofyearvalue,Datename( Day,'2015-04-30 01:01:01') asDayvalue,Datename(Week,'2015-04-30 01:01:01') asWeekvalue,Datename(Weekday,'2015-04-30 01:01:01') asWeekdayvalue,Datename(Hour,'2015-04-30 01:01:01') asHourvalue,Datename(Minute,'2015-04-30 01:01:01') asMinutevalue,Datename(Second,'2015-04-30 01:01:01') asSecondValue;SELECT  DATEPART( Year,'2015-04-30 01:01:01'),        DATEPART(Month,'2015-04-30 01:01:01'),        DATEPART(DayOfYear,'2015-04-30 01:01:01'); SELECT    DATEADD( Year,1,'2015-04-30 01:01:01') asYearadd,DATEADD(Month,2,'2015-04-30 01:01:01') asWeekdayadd,DATEADD(Hour,2,'2015-04-30 01:01:01') asHouradd;

SQL Server functions full solution < four > date and Time Functions