Monday, March 12, 2012

Formatting

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.
do you convert it to Char(7) after you add 1 ... so that will reformat it to the char rather than integer

No comments:

Post a Comment