Linq-To-Sql convert string to date with specific format

Question

I have a date stored as a string in a not so good format (dd-MM-yyyy). The SQL server cannot automatically convert this into a date, at least not with the settings that are on it, but with a little help it fixes it. The following SQL statement works perfectly:

select * from table
where convert(date,myValue,105) < GETDATE()

Problem I have is, how do I do this in LINQ? I tried different things manually and I tested Linqer to generate LINQ from my SQL. This is what Linqer gives me:

from table in db.table
where
  Convert.ToDateTime(myValue) < DateTime.Now
select new {
  ...
}

Problem is, when I run the statement above I get:

The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

Probably because it drops the 105 parameter to the convert method. I've tried different parse methods (Parse, ParseExact and Convert.ToDateTime with an IFormatProvider) but all those give me errors that are more or less like:

Method 'System.DateTime ParseExact(System.String, System.String, System.IFormatProvider)' has no supported translation to SQL.

So my question is, is there a good way to convert a date with an "odd" dateformat in Linq so that I will get working T-SQL? I already have a "working" solution, but I would like to know if there's any way to get Linq to interpret this in a nice way?

Blaire Andaloc · Accepted Answer · 2015-10-23 10:47:48Z

2

Although this is not the best practice but if it works then so be with it, I suggest you create a View in your table

  CREATE VIEW view_table 
  AS 
  select tbl_id, 
         convert(date,myValue,105) as myDate
  from table

so then from your linq

     from table in db.view_table
        where
        Convert.ToDateTime(myDate) < DateTime.Now
   select new {
      ...
   }

because the column myValue is already converted to 105 format.

answered Oct 23, 2015 at 10:47

Blaire Andaloc

1271 silver badge14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Markus Over a year ago

That is a possible solution, but I was more interested in solving it with linq without having to create new stuff in the database.

jhmt · Accepted Answer · 2015-10-23 10:55:16Z

1

It might be better to convert them locally with using AsEnumerable and DateTime.ParseExact.

from table in db.table.AsEnumerable()
where
  DateTime.ParseExact(myValue, "dd-MM-yyyy", CultureInfo.InvariantCulture) < DateTime.Now
select new {
  ...
}

answered Oct 23, 2015 at 10:55

jhmt

1,4211 gold badge11 silver badges15 bronze badges

2 Comments

Markus Over a year ago

Yes, I thought about this. But this solution will load all the data into memory right?

jhmt Over a year ago

Yes. So I think Blaire Andaloc's answer is better than mine from performance point of view:)

Magnus · Accepted Answer · 2019-04-03 08:58:16Z

0

You should be able to parse out the parts manually and build a new DateTime

from table in db.table
let myValue = new DateTime(Convert.ToInt32(yourdate.Substring(6,4)),Convert.ToInt32(yourdate.Substring(3,2)),Convert.ToInt32(yourdate.Substring(0,2)))
where
  myValue < DateTime.Now

select new { ... }

yourdate is a date in string dd-MM-yyyy format

edited Apr 3, 2019 at 8:58

Magnus

47.3k8 gold badges88 silver badges128 bronze badges

answered Apr 3, 2019 at 8:56

Programmer

1

Collectives™ on Stack Overflow

Linq-To-Sql convert string to date with specific format

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related