Firebird equivalent to start and end of the day

Question

I need to exchange current_date in Firebird stored proc to Delphi equivalent of StartOfTheDay and EndOfTheDay.

Delphi's StartOfTheDay returns 18.08.2017:00.00.00.001, EndOfTheDay returns 18.08.2017:23.59.59.999.

How to do it in Firebird ?

Answer 1

How to get start of the day from a DATE input?

You can write a command like this:

SELECT DATEADD(MILLISECOND, 1, CAST(CURRENT_DATE AS TIMESTAMP)) FROM RDB$DATABASE

In procedure it can be:

CREATE PROCEDURE MyProcedure
AS
DECLARE VARIABLE DayStart TIMESTAMP;
BEGIN
  DayStart = DATEADD(MILLISECOND, 1, CAST(CURRENT_DATE AS TIMESTAMP));
  ...
END

It casts DATE value to TIMESTAMP (because the DATEADD function is sensitive for the input data type) and then adds 1 millisecond to that value. For today you should get this (but I don't think it will match to that Delphi function result):

18.08.2017, 00:00:00.001

How to get end of the day from a DATE input?

Similarly to the start of the day, you can write this command to get end of the day:

SELECT DATEADD(MILLISECOND, -1, CAST(CURRENT_DATE + 1 AS TIMESTAMP)) FROM RDB$DATABASE

In procedure:

CREATE PROCEDURE MyProcedure
AS
DECLARE VARIABLE DayEnd TIMESTAMP;
BEGIN
  DayEnd = DATEADD(MILLISECOND, -1, CAST(CURRENT_DATE + 1 AS TIMESTAMP));
  ...
END

This one adds 1 day to the given DATE, then casts to the TIMESTAMP and subtracts 1 millisecond from it. For today it should return this (which matches the mentioned Delphi function):

18.08.2017, 23:59:59.999

How to get DATE from a TIMESTAMP input?

If your input is of TIMESTAMP type with a time portion specified, then first cast to a DATE to get rid of the TIME portion. For example to get day start from a TIMESTAMP value:

SELECT DATEADD(MILLISECOND, 1, CAST(CAST(CURRENT_TIMESTAMP AS DATE) AS TIMESTAMP)) FROM RDB$DATABASE

Or similarly in a stored procedure:

CREATE PROCEDURE MyProcedure
AS 
DECLARE VARIABLE DayEnd TIMESTAMP;
DECLARE VARIABLE DayStart TIMESTAMP;
DECLARE VARIABLE DateTime TIMESTAMP;
BEGIN
  DateTime = CURRENT_TIMESTAMP; -- this has time portion if not executed at midnight
  DayStart = DATEADD(MILLISECOND, 1, CAST(CAST(DateTime AS DATE) AS TIMESTAMP));
  DayEnd = DATEADD(MILLISECOND, -1, CAST(CAST(DateTime AS DATE) + 1 AS TIMESTAMP));
  ...
END

Answer 2

First, note Delphi implements the StartOfTheDay function as:

function StartOfTheDay(const AValue: TDateTime): TDateTime;
begin
  Result := Trunc(AValue);
end;

This means it does not return 18.08.2017:00.00.00.001 but 18.08.201700.00.00.000, witch is true.

---

In Firebird 3.0 and up, you can implement StartOfTheDay and EndOfTheDay routines as functions:

create or alter function STARTOFTHEDAY (
    STAMP timestamp)
returns timestamp
as
begin
  return cast(STAMP as date);
end

and

create or alter function ENDOFTHEDAY (
    STAMP timestamp)
returns timestamp
as
begin
  return dateadd(millisecond, -1, cast(cast(STAMP as date) + 1 as timestamp));
end

In older versions, embed the same code in stored procedures.

Testing and usage:

select
  -- Date type
  STARTOFTHEDAY(current_date),
  ENDOFTHEDAY(current_date),
  -- Timestamp type
  STARTOFTHEDAY(current_timestamp),
  ENDOFTHEDAY(current_timestamp)
from RDB$DATABASE

Today's results:

STARTOFTHEDAY: 22.09.2017 00:00:00.000
ENDOFTHEDAY:   22.09.2017 23:59:59.999