Unexpected result from MAX(ID) in SQL Server

Question

Max id is working fine until the id Q/21/9 but as soon as the id increments to Q/21/10. SQL Server keeps returning Q/21/9 as max id.

See in image. Max id Q/21/10 exists.

But SQL Server returns Q/21/9 whereas Q/21/10 is expected.

Below is my code that i am using to generate custom auto incremental id.

 private static int qid = 1;

 public string GetQuotationId()
 {
        Connection con = new Connection();

        if (ConnectionState.Closed == con.connect.State)
        {
            con.connect.Open();
        }

        string query = "SELECT MAX(QuotationReference) FROM Table_Inquiry";

        SqlCommand cmd = new SqlCommand(query, con.connect);

        try
        {
            var maxid = cmd.ExecuteScalar() as string;

            if (maxid == null)
            {
                return "Q/" + (DateTime.Today.Year % 100).ToString() + "/" + qid.ToString() + "";
            }
            else
            {
                int intval = int.Parse(maxid.Substring(5));
                intval++;
                return String.Format("Q/" + (DateTime.Today.Year % 100).ToString() + "/{0:}",intval);
            }
        }
        catch
        {
            throw;
        }
}

The columns seem to be a string (NVARCHAR?) - then MAX will determine the lexicographical highest value; where "9" >= "10" — Klaus Gütter, Feb 01 '21 at 12:57
No images please, post a plain text. This will help others to help you. — Serg, Feb 01 '21 at 12:58
It's far better to store separate components separately, and only merge them together into this string as a *presentation* matter. Keep *data* in the database, not arbitrarily formatted strings. — Damien_The_Unbeliever, Feb 01 '21 at 13:02
Maybe you can replace 9 with 09, If you want to compare string numbers the number of digits must be equal — Hamed Rashno, Feb 01 '21 at 13:32
See also [C# Data Connections Best Practice?](https://stackoverflow.com/questions/17552829/c-sharp-data-connections-best-practice) — Charlieface, Feb 01 '21 at 14:48

score 3 · Accepted Answer · answered Feb 01 '21 at 13:52

SQL Server is, of course, functioning as it was designed. The string Q/21/9 sorts after Q/21/10. They're strings, not numbers. So they sort in string order.

If your values were Q/21/09 and Q/21/10 you would not have this problem. Of course, you would also need Q/07/08 rather than Q/7/8 for that to work correctly.

What happened? You've joined the legions of programmers who have invented a new way of storing a date in your database. (You're in good company.) The date you're storing is the date of a month. For example, Q/21/9 means the month ending 2021-09-30 and Q/21/10 means 2021-10-31.

With respect, inventing new ways of storing dates almost always leads to the kind of trouble you have here. Dates are hard to get right. For future reference: if you find yourself thinking "I can store these month-end dates as Q/yy/m and save space," train yourself to think "no no no, that's a self-invented way of storing a date. That will break something in the future."

How can you solve your immediate problem?

Can you change your column values to include the leading zeros on your values when you write your database? If so, you will conceal your problem until 1-Jan-2100.
Can you change your table layout to use an autoincrementing ID column for your primary key, and add a month_ending column? That column should have a DATE datatype. It's easy to get the last day of the month with EOMONTH() SELECT EOMONTH(GETDATE()) AS month_ending; does it. Then you can use MAX(month_ending) to get the latest date.
You may be able to write a stored function that will convert Q/21/9 into Q/21/09, then do SELECT(MAX(dbo.myFunction(QuotationReference))) to get the value you need.
If your table is reasonably small your best bet may be to do the MAX search in your Winforms C# code rather than using sql's MAX() function.

Thank you for the explanation. I was thinking for the same but you have cleared it exactly how I can start with it, to resolve this issue. — Aaric Aaiden, Feb 01 '21 at 14:18
@AaricAaiden if this answers your question, you should accept it as an answer — Justin C, Feb 01 '21 at 15:19
And NEVER store a value that represents a year as a 2 digit number. Learn the lessons of Y2K! — SMor, Feb 01 '21 at 15:41
Could you tell me why i shouldn't save year as 2 digit. Because it is my requirement to have auto generated id in this format Q/2digityear/1... and the year should be updated automatically. — Aaric Aaiden, Feb 01 '21 at 15:45
I have successfully resolved issue by getting all QuotationReferences in a datatable then truncating undesired format by using substring(5). This returned me the only integer ids that i stored in a list of int type then using FindMaxId function i got the max id . Then i incremented and concatenated with desired string format and returned. Thanks for all of your suggestions. — Aaric Aaiden, Feb 01 '21 at 16:45

Unexpected result from MAX(ID) in SQL Server

1 Answers1