In our work, we often need to deal with the data of a certain period, for example: How to solve the sales volume of last week? How to solve the sales amount on the first day of last month? How to calculate the number of online users during the same period last year? . These are data that refer to a specific or current time period. How do we figure out the exact time period from a mountain of data?

We usually use some date functions when calculating time or calculating dates. Most of them are relatively common, such as YEAR(), MONTH(), DATEADD() and so on. Today I will explain some advanced date functions.

Test environment SQL Server 2017

@@DATEFIRST

Function Returns the current value of SET DATEFIRST for a specific session.

@ @ DATEFIRST grammar

SET DATEFIRST n specifies the first day of the week (Sunday, Monday, Sunday). N values range from 1 to 7.

For example, if we specify Sunday as the first day of the week, @@dateFirst today (2021-12-08) will return 1. Details are as follows:

SET DATEFIRST 1; SELECT @@dateFirst; -- Returns the value of DATEFIRSTCopy the code

Results:

This function may not seem very useful to many people, but it is actually very important to set up the first day of the week. In the United States, planet Day is the first day of the week, while in Korea, Monday is the first day of the week.

For example, if I want to know the sales volume on the third day of last week, you need to define the day of last week as the first day before you can further calculate the sales volume on the third day.

The system time

There is a set of functions for getting the system time. The GETDATE function is a common one, among others.

SELECT SYSDATETIME() SELECT SYSDATETIMEOFFSET() SELECT SYSUTCDATETIME() SELECT CURRENT_TIMESTAMP SELECT GETDATE() SELECT  GETUTCDATE()Copy the code

Results:

The system time including UTC is universal standard time, and the other time is the current time zone. Note: CURRENT_TIMESTAMP is equivalent to the GETDATE() function.

DATEADD

Function adds the specified number value (as a signed integer) to the specified datePart of the input date value, and returns the modified value.

Syntax DATEADD (datepart, number, date) The datepart parameter is defined by the system. The parameter list is as follows:

Example query last month today, next month today

SELECT DATEADD(month, -1, '20211208');
SELECT DATEADD(month, 1, '20211208');
Copy the code

Results:

DATEDIFF

Function returns the count (as a signed integer value) of the specified datePart boundary between the specified startDate and endDate.

Syntax DATEDIFF (datePart, startDate, enddate)

Note: The return value is the int difference between startDate and EndDate, represented by the boundary set by datePart.

Example calculates the number of days between the first day of last year and today

SELECT DATEDIFF(DAY,'20200101','20211208')
Copy the code

Results:

DATEFROMPARTS

Returns the date value mapped to the specified year, month, and day.

DATEFROMPARTS (year, month, day)

Note: DATEFROMPARTS returns a date value with the date part set to the specified year, month, and day, and the time part set to the default value. DATEFROMPARTS raises an error for invalid arguments. DATEFROMPARTS returns NULL if at least one of the required arguments has a NULL value. This function can perform remote processing on SQL Server 2012 (11.x) and later servers. It cannot fail to perform remote processing on servers with versions older than SQL Server 2012 (11.x).

The example returns today’s date by specifying a specific date:

SELECT DATEFROMPARTS ( 2021, 12, 8 ) AS Result;
Copy the code

The results of

DATENAME

Function returns a string representing the specified datePart of the specified date.

Syntax DATENAME (date, date)

The sample

SELECT DATENAME(year, getdate()) 'Year' 
    ,DATENAME(month, getdate()) 'Month'
    ,DATENAME(day, getdate()) 'Day'
    ,DATENAME(weekday,getdate()) 'Weekday';
Copy the code

Results:

Through this function, we can know the specific information such as the year, month, day and week of a specific day. This is especially used when grouping statistics.

DATEPART

Function Returns an integer representing the specified datePart of the specified date.

Syntax DATEPART (DATEPART, date)

The sample

SELECT DATEPART(year, getdate()) 'Year' 
    ,DATEPART(month, getdate()) 'Month'
    ,DATEPART(day, getdate()) 'Day'
    ,DATEPART(weekday,getdate()) 'Weekday';
Copy the code

Results:

Careful students may notice that DATEPART and DATENAME have a lot in common, but note that they return different types. DATEPART returns a numeric type and DATENAME returns a string type.

EOMONTH

Returns the last day of the month containing the specified date (with an optional offset)

Syntax EOMONTH (start_date [, month_to_add])

The example shows the last day of the month

DECLARE @date DATETIME = '20211208'; DECLARE @date DATETIME = '20211208' SELECT EOMONTH ( @date ) AS Result; GOCopy the code

Results:

Add the offset parameter month_to_add. For example, return the last day of the next month:

DECLARE @date DATETIME = '20211208';
SELECT EOMONTH ( @date,1 ) AS Result;
GO
Copy the code

Results:

ISDATE

Returns 1 if the expression is a valid date, time, or datetime value; Otherwise 0 is returned. If the expression is a datetime2 value, ISDATE returns 0.

Syntax ISDATE (expression)

Example Checks whether today (2021-12-08) is a valid date

IF ISDATE(' 21-12-08') = 1 SELECT * from (' 21-12-08') ELSE SELECT * from (' 21-12-08');Copy the code

Results:

Interested students can try to determine whether ‘2022-02-30’ is legal.

Practical application

1. The first day of the month

SELECT DATEADD (mm, DATEDIFF (mm, 0, getdate ()), 0) - 2021-12-01 00:00:00. 000Copy the code

Monday this week

SELECT DATEADD (wk, a DATEDIFF (wk, a 0, getdate ()), 0) - 2021-12-06 00:00:00. 000Copy the code

3. First day of the year

SELECT DATEADD (yy, DATEDIFF (yy, 0, getdate ()), 0) - 2021-01-01 00:00:00. 000Copy the code

The first day of the next quarter

SELECT DATEADD (qq, DATEDIFF (qq, 1, getdate ()), 0) - 2021-01-01 00:00:00. 000Copy the code

5. Last day of last month

The SELECT DATEADD (dd - DAY (getdate ()), getdate ()), the 2021-11-30 20:14:21. 850Copy the code

6. Last day of the year

SELECT DATEADD (year, DATEDIFF (year, 0, DATEADD (year 1, getdate ())), 1) - 2021-12-31 00:00:00. 000Copy the code

7. The same day last year

The SELECT DATEADD (YEAR 1, GETDATE ()), the 2020-12-08 20:19:05. 987Copy the code

So that’s the advanced date function we’re going to introduce today