How to apply Datetime comparison on varchar Datatype in C#

I want to apply datetime comparison on a field which is of type varchar in SQL Server database. I want to know how can i apply this, for example my query is something like this

from Pos in TableName
where Pos.vcr_Value >= Convert.ToDateTime("UserInputDate") //UserInputDate = 02/02/2011
select Pos;

If i try this it works

  • Dynamically create temp table based on resultset from SP
  • How to warm up a sleeping SQL Server database
  • Select Condition Based on Multiple rows
  • COUTING rows before OFFSET-FETCH
  • Auto increment primary key in SQL Server Management Studio 2012
  • Mysql to get the current date with time 23:59:59
  • from Pos in TableName
        where Pos.vcr_Value == "UserInputDate" //UserInputDate = 02/02/2011
        select Pos;

    But this just matches the date i want to get all the records after UserInputDate variable

    This field store variable type of data sometime it save string sometime datetime

  • How do I search for a ', ' and replace with ', '<CR><LF> in SQL Server Management Studio's editor
  • Getting all rows created today
  • Update Statement with Sub-query
  • Is it possible to insert timestamp in sql?
  • SSIS 2012 SQL Agent Job ConnectionString vs InitialCatalog
  • CREATE TRIGGER must be the first statement in a batch
  • 3 Solutions collect form web for “How to apply Datetime comparison on varchar Datatype in C#”

    As others have said, your database design is fundamentally poor. Given that the column may not even be a date at all, you’d need to use DateTime.TryParse or DateTime.TryParseExact to write something which would work in LINQ to Objects. In LINQ to SQL that won’t work as you’d need to use a lambda expression with a block as the body, and that can’t be converted into an expression tree.

    Furthermore, you’ve also got the problem of knowing what date format to expect – which is a natural corollary of storing dates in a text-based field instead of as a native date-based field.

    If your date format were yyyy-MM-dd (or yyyy/MM/dd) it would be possible to do something which at least approximated the right query, by doing a string comparison, e.g. saying the value had to be between “2011-02-02” and “2099-12-31” for example. Even then it would match values such as “201 this isn’t really a date”.

    If you don’t have too many records, you could always do the filtering at the .NET side instead of in SQL – it’s not ideal, but if you have to work with a broken schema, it’s probably the most reliable option. If at all possible, however, you should change how you store the data.

    Fix you database. Convert the varchar field to DateTime

    check here There are answers how to convert and compare dates


    Ivo Stoykov

    PS: Steve is right. date format kept in DB as varchar is very bad practice!

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.