MSSQL date and time functions

Несколько часто применяемых функций даты и времени в MSSQL.

CREATE FUNCTION [BEGDAY] (@DAT DATETIME)
RETURNS DATETIME
with schemabinding
AS
/* Возвращает начало дня: 2014-05-19 00:00:00.000 */
BEGIN
	DECLARE @RET DATETIME
	set @RET = null
	SET @RET=cast(cast(@DAT as date) as datetime)
	RETURN @RET
END
GO

CREATE FUNCTION [ENDDAY] (@DAT DATETIME)
RETURNS DATETIME
with schemabinding
AS
/* Возвращает конец дня: 2014-05-19 23:59:59.997 */
BEGIN
	DECLARE @RET DATETIME
	set @RET = null
	SET @RET=(convert(datetime, convert(varchar, @DAT, 101) + ' 23:59:59.998'))
	RETURN @RET
END
GO

CREATE FUNCTION [BEGMONTH] (@DAT DATETIME)
RETURNS DATETIME
with schemabinding
AS
/* Возвращает начало месяца: 2014-05-01 00:00:00.000 */
BEGIN
	DECLARE @RET DATETIME
	set @RET = null
	SET @RET=convert(date,dateadd(day,1-day(@DAT),@DAT))
	RETURN @RET
END
GO

CREATE FUNCTION [ENDMONTH] (@DAT DATETIME)
RETURNS DATETIME
with schemabinding
AS
/* Возвращает конец месяца: 2014-05-31 00:00:00.000 */
BEGIN
	DECLARE @RET DATETIME
	set @RET = null
	SET @RET=convert(datetime, convert(varchar, dateadd(month,1,dateadd(day,1-day(@DAT),@DAT))-1, 101))
	RETURN @RET
END
GO

Примеры вызова функций:

select [BEGDAY] ('2014-04-23 09:45:21') dt
select [BEGDAY] ('2014-04-23') dt
dt
--
2014-04-23 00:00:00.000

select [ENDDAY] ('2014-04-23 09:45:21') dt
select [ENDDAY] ('2014-04-23') dt
dt
--
2014-04-23 23:59:59.997

select [BEGMONTH] ('2014-04-23 09:45:21') dt
select [BEGMONTH] ('2014-04-23') dt
dt
--
2014-04-01 00:00:00.000

select [ENDMONTH] ('2014-04-23 09:45:21') dt
select [ENDMONTH] ('2014-04-23') dt
dt
--
2014-04-30 00:00:00.000

Расчет рабочих часов в указанном периоде

Я рассматривал случай рабочего дня с 08:00 по 17:00, включая обед с 12:00 по 13:00. Функция рассчитывает количество часов, которые попадают в заданный диапазон времени с учетом выходных дней (суббота и воскресенье), рамок рабочего дня и обеда.

CREATE OR REPLACE function worktime(beg_datetime in date, end_datetime in date)
return number
as
    kol_day number;
    type arr_table is table of number index by binary_integer;
    arr arr_table;
    res number default 0;
    
begin

   -- Чистим массив
   arr.delete;
   
 if beg_datetime < end_datetime then
   -- Расчет количества дней в заданном периоде дат
   kol_day := round(trunc(end_datetime) - trunc(beg_datetime));
   
   for i in 0..kol_day loop
      -- Проверка выходных дней (exclude Sunday, Saturday)
      if not to_char(beg_datetime + i, 'd') in (7, 1) then 
        -- Расчет количества рабочего времени по каждому дню
        arr(i) := case        
        -- если промежуточный день
        when not trunc(beg_datetime + i) in (trunc(beg_datetime), trunc(end_datetime)) 
            then 8
        -- если даты заданного дипазона совпадают
        when kol_day = 0 and trunc(beg_datetime + i) = trunc(end_datetime) then
           round(end_datetime - (beg_datetime + i), 3) 
        else case 
             -- если начало периода
             when trunc(beg_datetime + i) = trunc(beg_datetime) and
             to_char(beg_datetime, 'hh24') >= 8 and to_char(beg_datetime, 'hh24') < 12 
                then round((((trunc(beg_datetime) + 17/24) - beg_datetime)*24) - 1,3)
             when trunc(beg_datetime + i) = trunc(beg_datetime) and 
             to_char(beg_datetime, 'hh24') >= 13 and to_char(beg_datetime, 'hh24') < 17
                then round((((trunc(beg_datetime) + 17/24) - beg_datetime)*24),3)
             -- если конец периода
             when trunc(beg_datetime + i) = trunc(end_datetime) and
             to_char(end_datetime, 'hh24') >= 8 and to_char(end_datetime, 'hh24') < 12 
                then round((end_datetime - (trunc(end_datetime) + 8/24)) * 24,3)
             when trunc(beg_datetime + i) = trunc(end_datetime) and 
             to_char(end_datetime, 'hh24') >= 13 and to_char(end_datetime, 'hh24') < 17
                then round((end_datetime - (trunc(end_datetime ) + 8/24 + 1/24)) * 24,3)     
             -- ошибка
             else 0
             end
        end;
        
        -- Расчет суммароного рабочего времени за период
        res:= res + arr(i);
      
      end if;
                        
   end loop;

 else
 res:=-1;    
    DBMS_OUTPUT.PUT_LINE('Указан неверный диапазон дат!');
 
 end if;
   return res;
end;
/

Пример, вызова функции:

select worktime(to_date('07.04.2014 16:12:34','dd.mm.yyyy hh24:mi:ss'), to_date('20.04.2014 09:20:15','dd.mm.yyyy hh24:mi:ss')) RES from dual;

RES
---
72.791