SQL Server---Data type conversions during query

SQL Server---Data type conversions during query

The first two days in the maintenance of a city Talent service Center personnel file Management system, found this problem the new file number rule is the date + has the file maximum number +1 (six bits, less than six bits in the middle of 0) ((CONVERT ([varchar] (9), [Createtime], ( ) (+right) (100000000000.) +[num], (6))))) For example: 20150511007841. Honestly, I don't know why I used the maximum number, not the total number of records, +1 (no delete data), and then we'll talk about the problem and how to solve it.

Problem reproduction

Total number of records in query table +1

Select count (number) +1 from t_universitystudent

Execution Results

Querying the table for maximum data +1

Select MAX (number) +1fromt_universitystudent

Execution Results

Cause of the problem

Why is it that there are 7,840 data in the database, and the data record is 1 each time, why does it cause this problem?

I would like to have experienced developers, already know, is not the data type has a problem. 9>1000 is established in the comparison of the size of the string, which means that 999>7840 is also correct.

We sort it out to verify if the above statement is correct.

Select number from T_universitystudentorderbynumber ASC

Execution results

Problem solving

Now that we know that there is a problem with the field design process, the simplest part is that the data type of the field is changed, but this method is not the best because we do not understand the consequences of modifying the data type.

Then we can use the cast function and the CONVERT function.

The specific usage is as follows

Sort by converting a varchar type to an int type (using the CONVERT function)

Select number from T_universitystudentorderbyconvert (int,number) ASC

Execution results

Convert varchar in a query field to int

Select MAX (CAST (numberasint)) +1fromt_universitystudent

Execution results

It is important to note that the CONVERT function can only be used in SQL Server and that the cast function is available in Oracle and SQL Servers, and that the reader has to try it out for support in other databases.

SQL Server---Data type conversions during query