Calculation of dates in Firebird

Question

Calculation of dates in Firebird

Asked 10 years ago

Viewed 2,899 times

4

I am created a report to demonstrate the period of removal of employees, for that I need to calculate the days, months and years that the same stayed away, and I’m having a problem, in Firebird use something like datediff(day,dtini,dtfim), the problem is that if passed a period like 02/02/2014 and 02/02/2014 says there were 0 days of removal, a problem is that the datediff returns me one day less.

Another problem is that I do not know a function in Firebird that returns periods in days, months and years, already considering the number of days of each month, leaving a margin of error in the calculation since I am making this calculation approximately.

2

2-2==0, Isn’t it? To solve your problem isn’t just adding up 1 to the Datediff result? And what do you mean by error margin? How do you want to use error margin in a subtraction account?

– Math

2014/12/03 at 11:46
01/12/2014 At 30/01/2015 - my result returns 2 months and 1 day understands! adding +1.I do not want to use error margin, I want to take the margin, as above 2-2==0 +1 =1 only that in some cases it adds an extra day +1

– Balrog

2014/12/03 at 11:49
It is two months of 31 days. One date less the other gives 60 days difference. How you arrived at the value of 2 meses, Firebird itself does this conversion for you or you just split it by 30 to calculate how many months are?

– Math

2014/12/03 at 11:57
exemplifying again my problem, I need to calculate in a more "dynamic" way, because when it passes a month, I divide the value by 30, giving wrong values, because some months have 31 days, I tried to make a if checking whether the initial month is 31 days, but the calculation is wrong.

– Balrog

2014/12/03 at 12:00
Now I understand what you mean by the margin of error. I think there was a typo in your question, take a look at what I modified. By the way, are you working with some programming language together? For it can make this account of the difference of the days already considering the numbers of days of each month through the language itself, rather than using the bank for this.

– Math

2014/12/03 at 12:03
@Balrog, I’ve run some tests here and there’s nothing unusual about the function datadiff, what seems to be strange is your logic, I do not know how you did it: "01/12/2014 At 30/01/2015 - my result returns 2 months and 1 day understands", because this select datediff (day, date '1-Dec-2014', date '30-Jan-2015') from rdb$database, returns 60 days, so it’s correct, your problem seems to be converting days into years, months and days, right?

– Fernando Leal

2014/12/03 at 12:06
I’m working with Delhpi 7, and Firebird, but I have the following, it’s a report, the data is all in Firebird, yes the problem is in the conversion. the above example is one, but it may have long periods that I will need to calculate.

– Balrog

2014/12/03 at 12:22
@Balrog You have the start date of the departure and the date of the departure and want to return via query how many days away the individual was, right? Will be considered months of 30 days or counting in days same?

– Caputo

2014/12/03 at 13:30
@Caputo They will be considered months, when the days are greater than 30 I’m dividing by 30, I need to show the period that the individual was away as: 1 Year 2 Months and 25 days.

– Balrog

2014/12/03 at 13:38

Show 4 more comments

1 answer

Browser other questions tagged delphi delphi-7 firebird

You are not signed in. Login or sign up in order to post.

by Caputo • **5,583** points · Answer 1 · 2014-12-03T14:21:03+00:00

Delphi does not have a native function that returns the "humanized" date difference as @Omni said in chat, but you can use Delphi’s Dateutils to generate this information:

Note: In your case as a day to yourself is a day of departure, you will have to add 1 to the final date

function TForm1.PeriodoExtenso(const DataInicial, DataFinal: TDate): string;
var
  Anos, Meses, Dias: Integer;
  DataAjustada: TDateTime;
  Periodo: string;
begin
  Periodo := EmptyStr; //Variaveis locais no delphi não são inicializadas por padrao

  Anos := YearsBetween(DataInicial, DataFinal);
  DataAjustada := IncYear(DataInicial, Anos);
  Meses := MonthsBetween(DataAjustada, DataFinal);
  DataAjustada := IncMonth(DataAjustada, Meses);
  Dias := DaysBetween(DataAjustada, DataFinal);

  if Anos > 0 then
    Periodo := Format('%d anos', [Anos]);

  if Meses > 0 then
  begin
    if Periodo <> EmptyStr then
      Periodo := Periodo + ' e ';

    Periodo := Format('%s%d meses', [Periodo, Meses]);
  end;

  if Dias > 0 then
  begin
    if Periodo <> EmptyStr then
      Periodo := Periodo + ' e ';

    Periodo := Format('%s%d dias', [Periodo, dias]);
  end;

  Result := Periodo;
end;