LINQ to SQL Equivalent of ISDATE() in T-SQL and casting??

Question

Does anyone know the equivalent of ISDATE() in LINQ to SQL query syntax? I've got a varchar field in SQL that contains dates and I need to filter out the non-date rows.

was hoping for something like this:

var query = SomeDataContext;
    query = from p in query
    where p.ISDATE(field1) == true;
    select p;

also, how would one cast something for SQL in Linq syntax?

CAST(SomeDate AS SMALLDATETIME)

Answer 1

I don't think there is an extension method that maps to ISDATE when using LINQ to SQL.

If you are ok with loading all the data and then doing the filtering in the client space, then use TryParse on the field and compare to the date.

Otherwise, I would create a stored procedure which would return the data you want (so that you can use ISDATE), and then execute that through the context.

Also, your syntax for the use of ISDATE is incorrect. ISDATE just tells you if the expression is a valid date format. Your query would look like this:

var query = SomeDataContext;
    query = from p in query
    where ISDATE(field1) != 0 && CONVERT(datetime, field1) > some date
    select p;

The syntax isn't valid, but it gives you an idea of how to form the query.

Answer 2

Looks like you can do it. Links:

http://msdn.microsoft.com/en-us/library/bb386973.aspx

this might be of help but I haven't watched it:

http://mtaulty.com/videos/nuggets/l2s/15_mt_l2s_callingsqlfunctions.wmv

Full list here:

http://mtaulty.com/CommunityServer/blogs/mike_taultys_blog/archive/2007/05/10/9322.aspx

Answer 3

The trick to doing this is to create a user function in the database which merely calls ISDATE as returns the value

  CREATE FUNCTION My_ISDATE(@maybeDate varchar(max))
  returns bit
  as return ISDATE(@maybeDate);

Then add My_IsDate to you database context, and use it in you query:

var db = SomeDataContext;
var query = from p in db.MyTable
where db.My_ISDATE(p.field1)
select p;