Weird formatting problem

Hello all,
I have a strange problem that i need some advice on.

I have the following field called FILENO. It is a SQL 2000 field with the Data Type set to Char (7). The following sql statement works perfectly:

SELECT TOP 1 RTRIM(FILENO) AS TEST, RIGHT(DATEPART(Yy, FILENOYEAR), 2) AS YEAR FROM tblRecords
WHERE RIGHT(DATEPART(Yy, FILENOYEAR), 2) = '05'
ORDER BY FILENO DESC

It returns the correct data, 0050. Now, what i'm trying to do is add 1 to the value so i can get the next available number which is 0051. But, when i run the following sql statement, i get 51 instead of 0051.

SELECT TOP 1 RTRIM(FILENO+1) AS TEST, RIGHT(DATEPART(Yy, FILENOYEAR), 2) AS YEAR FROM tblRecords
WHERE RIGHT(DATEPART(Yy, FILENOYEAR), 2) = '05'
ORDER BY FILENO DESC

Does anyone have an idea how to solve this Thanks.

Richard M.

Answer this question

Weird formatting problem

Mr. Lee
When you do FILENO + 1, you are converting FILENO column to integer value and then adding one to it. RTRIM then implicitly converts the resulting integer value to string and displays. This doesn't pad the numeric value with zeroes. In the original expression, all you are doing is trimming the blanks. You can do one of the following:

1. RIGHT( REPLICATE('0', 4) + CAST(CAST( FILENO as int ) + 1 as varchar), 4) as TEST
2. Or change FILENO to integer data type. You can use a computed column with expression like #1 or use same expression in SELECT list

In general, you should try to avoid storing integer data in character columns and try to manipulate them. If you need something specific for display purposes, you can always change it at run-time. Storing numeric values in integer column for example provides optimized storage, domain validations etc.
Weird formatting problem

Answer this question