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

Source: Internet
Author: User

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

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.