CODEWITHSUNDEEP
entity-frameworkdatetimedecimal
Tharik Kanaka
Tharik Kanaka

Reputation: 2510

Decimal DateTime Conversion and Comparision in Entity Framework

I have some database values which store the start time of breaks and their duration, but the Time values are stored as Decimals. For an instance

12.45 = 12:45 PM
12:59 = 12:45 PM
13.0  = 13:00 PM

Duration is stored as int; 30 = 30 mins

Here I have to check if particular time is in between the range, so I have used C# and Entity Framework, and I have used the following code, but it gives an error that DateTime.Parse cannot be identified by LINQ.

STAR_TIME is the time which is stored as 12.45 and DURATION is stored as 30 and timE is a datetime type

meal ml = HRM.meals.SingleOrDefault(p => p.SHIFTCODE == ShiftCode
                               && DateTime.Parse(p.STAR_TIME.ToString().Replace(".", ":")) <= timE
                               && DateTime.Parse(p.STAR_TIME.ToString().Replace(".", ":")).AddMinutes(int.Parse(p.DURATION.ToString())) >= timE
                              );

Then instead of converting I tried to multiply by 60 and tried to compare values as below, but its logic is wrong. Here 12.59 is used as the time

  meal ml = HRM.meals.SingleOrDefault(p => p.SHIFTCODE == ShiftCode
  && p.STAR_TIME*60 <= new Decimal(12.59)*60
  && p.STAR_TIME*60 + p.DURATION >= new Decimal(12.59) * 60 
                             );

STAR_TIME -- 12.45 * 60 -- 747 DURATION -- 30 END TIME -- 747+ 30 = 777

But actually end time should be 13.15, 13.15 *60 -- 789

Can someone suggest any method to tackle this situation?

Upvotes: 0

Views: 481

Answers (1)

Jonathan Leffler
Jonathan Leffler

Reputation: 754570

Using decimals to represent time makes life hard. Simply multiplying by 60 is not going to give an answer in hours and minutes. For example, consider: 0.02 * 60. As a decimal, that gives the value 1.20. However, that should be 2 minutes, not 1.20 minutes.

You'll probably need to split the fraction from the integer. Let's consider 2.03 (2:03 AM). Multiply the integer by 60; that's 120 minutes since midnight. Multiply the 0.03 by 60; that gives you 1.80; divide that by 0.60 (since 60 minutes are represented by the fraction 0.60)and you get 3, so the total time is 123 minutes since midnight. Of course, you could simply multiply the fraction by 100; that gives the same result. If you have I.F (meaning integer I and fraction F), you obtain the total number of minutes since midnight from (60 * I + 100 * F).

Now suppose you have interval V too. This is an integer number of minutes.

Given:

  • I = 12
  • F = 0.45
  • V = 30

You want the value of I.F + V as a time.

  • M = 60 * I + 100 * F + V
  • M = 720 + 45 + 30
  • M = 795 since midnight

To get back to the answer H.M, you need to divide M / 60 to get H, and you take the remainder M % 60 and divide by 100 to get the fraction:

  • H = 795 / 60 = 13
  • M = (795 % 60) / 100 = 15 / 100 = 0.15

Hence, the answer is H + M = 13.15 or 13:15 PM.

Yes, it is hard work. But that's because the representation used is really not appropriate for the task at hand.

Upvotes: 2

Related Questions