Datumsfunktionen

Themen rund um den praktischen Einsatz von Firebird. Fragen zu SQL, Performance, Datenbankstrukturen, etc.

Moderator: thorben.braun

Antworten
sroland
Beiträge: 5
Registriert: Fr 27. Jul 2018, 08:38

Hallo Ihr, finde es toll dass hier ein deutsche Firebirdforum aufgemacht hat.
Werde in nächster Zeit ein paar nützliche Stored Procedure's posten vielleicht hilft es euch.

Code: Alles auswählen

create or alter procedure ERSTER_LETZTER_TAG (
    input date,
    TYP integer,
    MIT_UHRZEIT integer)
returns (
    RESULT date,
    TYPSTRING varchar(50),
    TYP_OUT integer,
    OUT_WEEKDAY integer,
    OUT_DAY integer,
    OUT_QUARTER integer,
    OUT_WEEK integer,
    OUT_YEARDAY integer)
AS
declare variable sqlDialect integer;
declare variable offset integer;
begin
  Mit_Uhrzeit  = coalesce(Mit_Uhrzeit,0);
  sqlDialect = 1;
  if (sqlDialect = 1) then
   input = cast(substring(input from 1 for 11) as Date);
  if ((typ = 1) or (typ = 0))  then --Datum
    result = cast(input as Date);
    typString = 'date_current_date';
    out_weekday = extract(weekday from result);
    out_day = extract(day from result);
    out_week = extract(week from result);
    out_yearday = extract(YEARDAY from result);
    out_quarter = (select result from quarter(:result));
    typ_out = 1;
    if (typ = 0) then suspend;

  if ((typ = 2) or (typ = 0)) then
  begin --start_of_last_week
    result=  dateadd(week,  -1 ,Dateadd(day ,-extract(weekday  from input) +1,input));
    typString = 'start_of_last_week';
    typ_out = 2;
    out_weekday = extract(weekday from result);
    out_day = extract(day from result);
    out_week = extract(week from result);
    out_yearday = extract(YEARDAY from result);
    out_quarter = (select result from quarter(:result));
    if (typ = 0) then suspend;
  end
  if ((typ = 3) or (typ = 0)) then
  begin --end_of_last_week
    result=  dateadd(week,  -1 ,Dateadd(day ,-extract(weekday  from input)+7,input));
    if (Mit_Uhrzeit= 1) then
    begin
      result = dateadd(millisecond,  -1 ,Dateadd(day , +1,result));
    end

    typString = 'end_of_last_week';
    typ_out = 3;
    out_weekday = extract(weekday from result);
    out_day = extract(day from result);
    out_week = extract(week from result);
    out_yearday = extract(YEARDAY from result);
    out_quarter = (select result from quarter(:result));

    if (typ = 0) then suspend;
  end
  if ((typ = 4) or (typ = 0)) then
  begin --end_of_last_week
    result=  dateadd(DAY,  -extract(weekday  from input)+1,input);
    typString = 'start_of_current_week';
    typ_out = 4;
    out_weekday = extract(weekday from result);
    out_day = extract(day from result);
    out_week = extract(week from result);
    out_yearday = extract(YEARDAY from result);
    out_quarter = (select result from quarter(:result));
    if (typ = 0) then suspend;
  end
  if ((typ = 5) or (typ = 0)) then
  begin --end_of_current_week
    result=  dateadd(DAY,  -extract(weekday  from input)+7,input);
    if (Mit_Uhrzeit= 1) then
    begin
      result = dateadd(millisecond,  -1 ,Dateadd(day , +1,result));
    end
    typString = 'end_of_current_week';
    typ_out = 5;
    out_weekday = extract(weekday from result);
    out_day = extract(day from result);
    out_week = extract(week from result);
    out_yearday = extract(YEARDAY from result);
    out_quarter = (select result from quarter(:result));

    if (typ = 0) then suspend;
  end
  if ((typ = 6) or (typ = 0)) then
  begin --start_of_next_week
    result=  dateadd(DAY,  -extract(weekday  from input)+1,input);
    result=  dateadd(Week ,1,  result);
    typString = 'start_of_next_week';
    typ_out = 6;
    out_weekday = extract(weekday from result);
    out_day = extract(day from result);
    out_week = extract(week from result);
    out_yearday = extract(YEARDAY from result);
    out_quarter = (select result from quarter(:result));
    if (typ = 0) then suspend;
  end
  if ((typ = 7) or (typ = 0)) then
  begin --end_of_next_week
    result=  dateadd(DAY,  -extract(weekday  from input)+7,input);
    result=  dateadd(Week ,1,  result);
    if (Mit_Uhrzeit= 1) then
    begin
      result = dateadd(millisecond,  -1 ,Dateadd(day , +1,result));
    end
    typString = 'end_of_next_week';
    typ_out = 7;
    out_weekday = extract(weekday from result);
    out_day = extract(day from result);
    out_week = extract(week from result);
    out_yearday = extract(YEARDAY from result);
    out_quarter = (select result from quarter(:result));
    if (typ = 0) then suspend;
  end
  if ((typ = 8) or (typ = 0)) then
  begin --start_of_last_month
    result=  dateadd(DAY,  -(extract(day  from input)-1),input);
    result=  dateadd(MONTH ,-1,  result);
    typString = 'start_of_last_month';
    typ_out = 8;
    out_weekday = extract(weekday from result);
    out_day = extract(day from result);
    out_week = extract(week from result);
    out_yearday = extract(YEARDAY from result);
    out_quarter = (select result from quarter(:result));
    if (typ = 0) then suspend;
  end
  if ((typ = 9) or (typ = 0)) then
  begin --end_of_last_month
    result=  dateadd(DAY,  -(extract(day  from input)),input);
    if (Mit_Uhrzeit= 1) then
    begin
      result = dateadd(millisecond,  -1 ,Dateadd(day , +1,result));
    end
    typString = 'end_of_last_month';
    typ_out = 9 ;
    out_weekday = extract(weekday from result);
    out_day = extract(day from result);
    out_week = extract(week from result);
    out_yearday = extract(YEARDAY from result);
    out_quarter = (select result from quarter(:result));

    if (typ = 0) then suspend;
  end
  if ((typ = 10) or (typ = 0)) then
  begin --start_of_current_month
    result=  dateadd(DAY,  -(extract(day  from input)-1),input);
    typString = 'start_of_current_month';
    typ_out = 10;
    out_weekday = extract(weekday from result);
    out_day = extract(day from result);
    out_week = extract(week from result);
    out_yearday = extract(YEARDAY from result);
    out_quarter = (select result from quarter(:result));

    if (typ = 0) then suspend;
  end
  if ((typ = 11) or (typ = 0)) then
  begin --end_of_current_month
    result=  dateadd(Month,  +1,input);
    result=  dateadd(DAY,  -(extract(day  from result)),result);
    if (Mit_Uhrzeit= 1) then
    begin
      result = dateadd(millisecond,  -1 ,Dateadd(day , +1,result));
    end
    typString = 'end_of_current_month';
    typ_out = 11;
    out_weekday = extract(weekday from result);
    out_day = extract(day from result);
    out_week = extract(week from result);
    out_yearday = extract(YEARDAY from result);
    out_quarter = (select result from quarter(:result));

    if (typ = 0) then suspend;
  end
  if ((typ = 12) or (typ = 0)) then
  begin --start_of_next_month
    result=  dateadd(Month,  +1,input);
    result=  dateadd(DAY,  -(extract(day  from result)-1),result);
    typString = 'start_of_next_month';
    typ_out = 12;
    out_weekday = extract(weekday from result);
    out_day = extract(day from result);
    out_week = extract(week from result);
    out_yearday = extract(YEARDAY from result);
    out_quarter = (select result from quarter(:result));

    if (typ = 0) then suspend;
  end
  if ((typ = 13) or (typ = 0)) then
  begin --end_of_next_month
    result=  dateadd(Month,  +2,input);
    result=  dateadd(DAY,  -(extract(day  from result)),result);
    if (Mit_Uhrzeit= 1) then
    begin
      result = dateadd(millisecond,  -1 ,Dateadd(day , +1,result));
    end
    typString = 'end_of_next_month';
    typ_out = 13;
    out_weekday = extract(weekday from result);
    out_day = extract(day from result);
    out_week = extract(week from result);
    out_yearday = extract(YEARDAY from result);
    out_quarter = (select result from quarter(:result));

    if (typ = 0) then suspend;
  end
  if ((typ = 14) or (typ = 0)) then
  begin --start_of_last_year
    result=  dateadd(Year,  -1,input);
    result=  dateadd(DAY,  -(extract(day  from result)-1),result);
    result=  dateadd(MONTH,  -(extract(month  from result)-1),result);
    typString = 'start_of_last_year';
    typ_out = 14;
    out_weekday = extract(weekday from result);
    out_day = extract(day from result);
    out_week = extract(week from result);
    out_yearday = extract(YEARDAY from result);
    out_quarter = (select result from quarter(:result));

    if (typ = 0) then suspend;
  end
  if ((typ = 15) or (typ = 0)) then
  begin --end_of_last_year
    result=  CAST(DATEADD(day, -1, cast('01.01.' || extract(YEAR from input) as Date)) AS DATE);
    if (Mit_Uhrzeit= 1) then
    begin
      result = dateadd(millisecond,  -3 ,Dateadd(day , +1,result));
    end
    typString = 'end_of_last_year';
    typ_out = 15;
    out_weekday = extract(weekday from result);
    out_day = extract(day from result);
    out_week = extract(week from result);
    out_yearday = extract(YEARDAY from result);
    out_quarter = (select result from quarter(:result));

    if (typ = 0) then suspend;
  end
  if ((typ = 16) or (typ = 0)) then
  begin --start_of_current_year
    result=  dateadd(DAY,  -(extract(day  from input)-1),input);
    result=  dateadd(MONTH,  -(extract(month  from result)-1),result);
    typString = 'start_of_current_year';
    typ_out = 16;
    out_weekday = extract(weekday from result);
    out_day = extract(day from result);
    out_week = extract(week from result);
    out_yearday = extract(YEARDAY from result);
    out_quarter = (select result from quarter(:result));

    if (typ = 0) then suspend;
  end
  if ((typ = 17) or (typ = 0)) then
  begin --start_of_current_year
    result=  CAST(DATEADD(day, -1, cast('01.01.' || extract(YEAR from input) as Date)) AS DATE);
    result=  dateadd(year ,  1,result);
    typString = 'end_of_current_year';
    if (Mit_Uhrzeit= 1) then
    begin
      result = dateadd(millisecond,  -1 ,Dateadd(day , +1,result));
    end
    typ_out = 17;
    out_weekday = extract(weekday from result);
    out_day = extract(day from result);
    out_week = extract(week from result);
    out_yearday = extract(YEARDAY from result);
    out_quarter = (select result from quarter(:result));

    if (typ = 0) then suspend;
  end

  if ((typ = 18) or (typ = 0)) then
  begin --start_of_next_year
    result=  dateadd(DAY,  -(extract(day  from input)-1),input);
    result=  dateadd(MONTH,  -(extract(month  from result)-1),result);
    result=  dateadd(year ,  1,result);
    typString = 'start_of_next_year';
    out_weekday = extract(weekday from result);
    out_day = extract(day from result);
    out_week = extract(week from result);
    out_yearday = extract(YEARDAY from result);
    out_quarter = (select result from quarter(:result));
    typ_out = 18;
    if (typ = 0) then suspend;
  end
  if ((typ = 19) or (typ = 0)) then
  begin --end_of_next_year
    result=  CAST(DATEADD(day, -1, cast('01.01.' || extract(YEAR from input) as Date)) AS DATE);
    result=  dateadd(year ,  2,result);
    if (Mit_Uhrzeit= 1) then
    begin
      result = dateadd(millisecond,  -1 ,Dateadd(day , +1,result));
    end
    typString = 'end_of_next_year';
    typ_out = 19;
    out_weekday = extract(weekday from result);
    out_day = extract(day from result);
    out_week = extract(week from result);
    out_yearday = extract(YEARDAY from result);
    out_quarter = (select result from quarter(:result));
    if (typ = 0) then suspend;
  end
 if (typ <> 0) then suspend;
 /*

 SELECT DATEADD(qq,DATEDIFF(qq,0,GETDATE())-1,0) AS FirstDayOfLastQuarter
, DATEADD(qq,DATEDIFF(qq,0,GETDATE()),-1) AS LastDayOfLastQuarter
, DATEADD(qq,DATEDIFF(qq,0,GETDATE()),0) AS FirstDayOfQuarter
, DATEADD(qq,DATEDIFF(qq,0,GETDATE())+1,-1) AS LastDayOfQuarter ;

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) , 0) AS Heute
, DATEDIFF(qq,0,GETDATE()) AS [Quartale Seit Anfang = X]
, CAST(0 AS DATETIME) AS Anfang
, DATEADD(qq,DATEDIFF(qq,0,GETDATE())-1,0) AS [Anfang Plus X-1 Quartale]
, DATEADD(qq,DATEDIFF(qq,0,GETDATE()),-1) AS [Anfang Plus X Quartale -1 Tag]
, DATEADD(qq,DATEDIFF(qq,0,GETDATE()),0) AS [Anfang Plus X Quartale +0 Tage]
, DATEADD(qq,DATEDIFF(qq,0,GETDATE())+1,-1) AS [Anfang Plus X+1 Quartale -1 Tag]
 */


end

einfach mit Ibexpert oder Flamerobin in eure Datenbank und mit
"select RESULT, TYPSTRING, TYP_OUT, OUT_WEEKDAY, OUT_DAY, OUT_QUARTER, OUT_WEEK, OUT_YEARDAY
from ERSTER_LETZTER_TAG('now', 0, 0) " probieren.

es könnte auch helfen die FreeAdhoc UDF's loszuwerden


Gruß Roland
lg Roland Siegel
s-plus GmbH
schreiner-plus.de
Antworten