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