SQL Server date functions CAST and CONVERT and their usage in business

I recently transferred from the client to the backend writing service. It is completely a small bit of white for the background database and service writing, so there is definitely not much technical content recently written.

First, let us figure out the problem: the report on the error. The fields mainly include the reported error ID (ErrorID), the reported person (ReportPerson), and the reported time (ReportTime) is accurate to milliseconds, the current statistics are as follows: (1) count the number of errors reported by each person per day within a certain period of time (accurate to milliseconds) (beginTime, endTime) (2) count the total number of errors reported by each person by month from the start time to the end time to the month in a certain period of time.

When you see the problem, you first think of "group by ReportPerson" to calculate the statistics of each person. However, you still need to obtain the daily statistics of each person before the statistics are met, for cainiao like me, it is a little troublesome and I don't know how to start it. I found a master in the SQL group and told me to set the time format, then, the time format is limited to days and months, and the two problems are solved.

I have listed multiple time functions in the previous article. Now I will introduce the CAST and CONVERT used in this article in detail:

Let's take a look at their syntax:

CAST (expression AS data_type [(length)])
CONVERT (data_type [(length)], expression [, style])

Where:

Expression: Any valid expression.
Data_type: Target data type. This includes xml, bigint, and SQL _variant. The alias data type cannot be used.
Length: an optional integer that specifies the length of the target data type. The default value is 30.
Style: specifies how the CONVERT function converts the integer expression of expression. If the style is NULL, NULL is returned. This range is determined by data_type.
Related to this article mainly include length and expression. length refers to the length of the target type, which is used to limit the precision of time. expression is used to limit the output time format, for example: yyyy/mm/dd/yyyy-mm-dd.

For more information about express and examples, see the following blog:
Http://www.jb51.net/article/33330.htm

Now, I am using this knowledge to solve my problem. The first is to count the reported volume by day, accurate to the daily time limit: CONVERT (varchar (11), ReportTime, 20) that is, yyyy-mm-dd
Then the group by problem is solved. The SQL code is:
Copy codeThe Code is as follows:
Select ReportPerson, CONVERT (varchar (11), ReportTime, 20) as 'reportime', count (*) as reportTotal from PCR_ConstructInfo where (ReportTime> '2017-11-15 12:11:12. 23 ') and (ReportTime <'2014-1-16 12:11:12. 23 ') group by ReportPerson, CONVERT (varchar (11), ReportTime, 20)

The execution result is:

Another problem is to collect statistics on the number of errors reported by each person on a monthly basis. You only need to set the time and date to the month, that is, CONVERT (varchar (7), ReportTime, 20) run the following SQL statement in yyyy-mm:
Copy codeThe Code is as follows:
Select ReportPerson, CONVERT (varchar (7), ReportTime, 20) as 'reporttime', count (*) as reportTotal from PCR_ConstructInfo where (ReportTime> '2017-11-1 ') and (ReportTime <'2014-2 -1 ') group by ReportPerson, CONVERT (varchar (7), ReportTime, 20)

The execution result is:

Now, let's end today. If any master has a better method, please leave a message !!!