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

SQL Server date function CAST and CONVERT and how to use it in business, many novices are not very clear about this, in order to help you solve this problem, the following small series will explain in detail for everyone, people who have this need can learn, I hope you can gain something.

Recently, I have just transferred from the client to the background writing service. The writing of the background database and service is completely white, so the recent writing must not have too much technical content. First of all, put out the problems encountered: it is still the error report, the fields mainly include Error ID, ReportPerson, and ReportTime accurate to milliseconds. The statistics to be done now are:

(1) Count the number of errors reported by each person every day within a certain period of time [start time, end time accurate to milliseconds]

(2) Count the total number of errors reported by each person within a certain period of time (originTime,endTime) by month.

The first thing that comes to mind when you see the problem is to group by ReportPerson, which can calculate the statistical data of each person, but it has not yet reached the requirements. You also need to obtain the statistical data of each person every day, specify the time format, and then limit the time format to days and months. These two problems are solved.

Now let's talk about CAST and CONVERT used in this article:

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. Alias data types cannot be used.

length: Optional integer specifying the length of the target data type. The default value is 30.

style: Specifies how the CONVERT function converts integer expressions of expression.

Returns NULL if the style is NULL. The range is determined by data_type.

Length refers to the length of the target type, which is used to limit the precision of the time. Expression is used to limit the format of the output time. For example: yy/mm/dd/y-mm-dd, etc.

Now I will use this knowledge to solve my problem. First of all, I will count the amount of reports by day, and the time limit of the day is accurate:

CONVERT(varchar(11) , ReportTime , 20 ) i.e. y-mm-dd Then the group by problem is solved, sql code is:

The code is as follows:

select ReportPerson,CONVERT(varchar(11) , ReportTime , 20 ) as 'ReporTime', count(*) as reportTotal from PCR_ConstructInfo where (ReportTime>'2012-11-15 12:11:12.23')and (ReportTime'2012-11-1')and (ReportTime

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.