C# MySQL LINQ DateTime conversion

Question

I'm trying to retrieve records from a mySQL DB using LINQ and C#.

The date in c# code is a string: 23-01-2010

I need to convert this to DateTime format as 2010-01-23 (mySQL default DateTime format), otherwise the query does not return any records, at present it errors saying a string cannot be matched against a DateTime (row.DateOfIssue) If I convert the string to DateTime (C#), then it is not in the mySQL DateTime format of yyyy-MM-dd

String endDate = "23-01-2010"; 
var query = (from row in uow.UserPersonalLicenseDetails
                             where (endDate >= row.DateOfIssue && endDate <= row.DateOfExpiry)
                             select row)

This is such a standard query it seems mad that it is so hard to do in LINQ. It seems putting any method like CompareTo etc in the where clause causes an error of "Search for a class that works in this scenario"

I'm now wondering if the best line of attack might be to write a stored procedure in the database. This could then take the C# datetime as a parameter, convert it to mySQL format and then run the required query..... Any thoughts?

this may help http://stackoverflow.com/questions/3633262/convert-datetime-for-mysql-using-c-sharp — user1208484, May 04 '12 at 12:15

Chris · Answer 1 · 2012-05-04T14:45:08.820

2

Make it a DateTime - so

var myDate = DateTime.Parse(endDate);

Then

myDate.ToString("yyyy-MM-dd");

---------- or this:

    var myDate = DateTime.Parse(endDate); 
var query = (from row in uow.UserPersonalLicenseDetails 
where ((myDate.CompareTo(row.DateOfIssue)>=0 && (myDate.CompareTo(row.DateOfExpiry)<=0) 
select row

edited May 04 '12 at 14:45

answered May 04 '12 at 12:13

Chris

2,471
25
36

This gives the error: "Operator >= cannot be applied to operands of type String and System.DateTime – user1079925 May 04 '12 at 12:42
this does?: String endDate = "23-01-2010"; var myDate = DateTime.Parse(endDate).ToString("yyyy-MM-dd"); var query = (from row in uow.UserPersonalLicenseDetails where (myDate >= row.DateOfIssue && myDate <= row.DateOfExpiry) select row) – Chris May 04 '12 at 12:56
how about this: var myDate = DateTime.Parse(endDate); var query = (from row in uow.UserPersonalLicenseDetails where ((myDate.CompareTo(row.DateOfIssue)>=0 && (myDate.CompareTo(row.DateOfExpiry)<=0) select row – Chris May 04 '12 at 14:44

Mathew Thompson · Answer 2 · 2012-05-04T16:38:50.357

0

Just convert your date string to DateTime and then in the LINQ convert the string to DateTime that's coming back in order to do the comparison. I've used ParseExact because we need to make sure that we are parsing to the exact format that MySQL stores the date in, which is yyyy-MM-dd hh:mm.

Something like:

var endDate = DateTime.Parse("23-10-2010").ToString("yyyy-MM-dd hh:mm");
var formattedEndDate = DateTime.Parse(endDate);
//you could cut that to one line by putting the date in said format in the first place

var query = (from row in uow.UserPersonalLicenseDetails
             where formattedEndDate >= row.DateOfIssue
                 && formattedEndDate <= row.DateOfExpiry
             select row)

edited May 04 '12 at 16:38

answered May 04 '12 at 15:04

Mathew Thompson

55,877
15
127
148

this looks promising, however, ParseExact expects the first parmaeter to be of String, but row.DateOfIssue is a Date in the database......thx – user1079925 May 04 '12 at 15:34
Ah I thought they were strings, updated my answer to reflect :) – Mathew Thompson May 04 '12 at 15:37
I don't think this will work. ParseExact expects the format of parameter 1 to be exactly the same as specified by parameter 2. – user1079925 May 04 '12 at 16:13
Edited! Yeah sorry, it needs to be parsed to string with that format, then parsed again (you could cut this out by just doing `var endDate = DateTime.Parse("2010-10-23 00:00");`, but if your end date is going to be a parameter passed in, then I guess it's easier to do it the way I've done it in the edit :) – Mathew Thompson May 04 '12 at 16:40
Sorry, this won't work as the second parse converts the date back to C# format, rather than keeping it in yyyy-MM-dd format....sob sob...this is very painful – user1079925 May 04 '12 at 16:48
Have you tried it using that code? Although it'll be in that format, when you are retrieving the dates from MySQL into your `row` objects it should automatically convert the format into one that matches the C# format? – Mathew Thompson May 07 '12 at 14:07
Yes, I have tried it, but it seems that the SQL generated by LINQ is converting the date back to: dd/MM/yyyy format... – user1079925 May 08 '12 at 14:30

score 0 · Answer 3 · answered May 17 '12 at 20:43

0

Ok, problem was that the Table had the field defined as DATE rather than DATETIME so no match was being made.

Used DateTime.Date and the match was made.

answered May 17 '12 at 20:43

user1079925

541
2
8
20

C# MySQL LINQ DateTime conversion

3 Answers3