Datumsfunktionen
Verfasst: Fr 27. Jul 2018, 09:31
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.
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
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
"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