Comparison of >= and <= with strings

Asked

Viewed 844 times

5

I have a field in the database which is in the following format: YYYYMM

Using the past I can normally search these fields:

WHERE I.DT_INCL >= @inicio   
AND I.DT_INCL <= @final

When trying to pass the query to Ingl returns the following error: Operator '>=' cannot be Applied to operands of type 'string' and 'string'

(from incorporacao in Incorporacao.FoundEntities
where incorporacao.DataInclusao >= inicio && incorporacao.DataInclusao <= final
select incorporacao).ToList();

I arrived at two possible solutions:

Create a method that returns a DateTime always putting the day fixed or leaving the field as whole.

Which of these two solutions is the most correct in this context? The database is old and I cannot modify it.

c# linq

  • The ideal is to use Datetime or Smalldatetime fields in the database to make comparisons. When using Nvarchar or Varchar for dates in the database, you run the risk of finding something other than a date, then exceptions may occur.

3 answers

6


I have a project here with a similar problem. I use integers to store a year and month representation in format YYYYMM no problem. This way an integer simulating a more recent date will always be larger than an older date.

  • 2

    Friend, use date fields for date, integers for integers. This your solution does not conform to good programming practices.

  • the point is that it needs year and month, not the full date, but a way to store a year and month representation in YYYYMM format. For this the int works very well.

  • Exactly, I apologize for not having understood well. Edit your reply so I can vote in favor.

  • No problem, thanks a lot.

2

In the stored code will be executed by the database engine. It tries to convert String in Date.

In C# there is no such thing, so you should use data types that are comparable. I suggest you convert the Strings to Datetime to use in the LINQ expression.

  • 1

    This response is ideal if the seat structure cannot be modified. Programmers tend to use string to save dates for fear of using Date or Datetime, ideally using the proper structure for the data type.

  • Remember that if you need to convert the stored data to be able to filter it you will need to do it on all table data, it is not very efficient. The ideal is to store with the right types. As a whole can work but I would still use a date type.

1

From what I understand, I believe you’re looking for the most readable solution. In that case it would declare the date format and make the comparison by the method DateTime.ParseExact:

        string dateString1 = "201406";
        string dateString2 = "201407";
        string formatString = "yyyyMM";

        if (DateTime.ParseExact(dateString1, formatString, null) < DateTime.ParseExact(dateString2, formatString, null))
        {
            //Código...
        }

ps. The most performatic would in fact be the comparison of integers.

Browser other questions tagged c# linq

You are not signed in. Login or sign up in order to post.