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
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
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
PS: Steve is right. date format kept in DB as varchar is very bad practice!