USP_RESOLVESHOWDATEFILTER

create procedure dbo.USP_RESOLVESHOWDATEFILTER
(            
    @SHOWCODE smallint = 0,
    @STARTDATE datetimeoffset = null output,
    @ENDDATE datetimeoffset = null output,
    @STARTTIME char(4) = null,
    @ENDTIME char(4) = null,
    @REFERENCEDATE datetimeoffset = null
)
as

set nocount on;

declare @currentDate datetimeoffset(3);
if @REFERENCEDATE is not null
    set @currentDate = switchoffset(@REFERENCEDATE, 0);
else
    set @currentDate = switchoffset(sysdatetimeoffset(), 0);

if @SHOWCODE = 0 -- All

    begin
        set @STARTDATE = null;
        set @ENDDATE = null;
    end

else if @SHOWCODE = 1 -- Today

    begin
        set @STARTDATE = dbo.UFN_DATETIMEOFFSET_GETEARLIESTTIME(@currentDate);
        set @ENDDATE = dbo.UFN_DATETIMEOFFSET_GETLATESTTIME(@currentDate);        
    end

else if @SHOWCODE in (2, 3, 4, 5, 6, 7, 8, 9) 
    begin
        set @ENDDATE = dbo.UFN_DATETIMEOFFSET_GETLATESTTIME(@currentDate);

        if @SHOWCODE = 2        -- 24h

            set @STARTDATE = dateadd(hour, -24, @currentDate);
        else if @SHOWCODE = 3   -- 48h

            set @STARTDATE = dateadd(hour, -48, @currentDate);
        else if @SHOWCODE = 4   -- 1w

            set @STARTDATE = dbo.UFN_DATETIMEOFFSET_GETEARLIESTTIME(dateadd(day, -6, @currentDate));
        else if @SHOWCODE = 5   -- 2w

            set @STARTDATE = dbo.UFN_DATETIMEOFFSET_GETEARLIESTTIME(dateadd(day, -13, @currentDate));
        else if @SHOWCODE = 6   -- 1m

            set @STARTDATE = dbo.UFN_DATETIMEOFFSET_GETEARLIESTTIME(dateadd(day, -29, @currentDate));
        else if @SHOWCODE = 7   -- 3m

            set @STARTDATE = dbo.UFN_DATETIMEOFFSET_GETEARLIESTTIME(dateadd(day, -89, @currentDate));
        else if @SHOWCODE = 8   -- 6m

            set @STARTDATE = dbo.UFN_DATETIMEOFFSET_GETEARLIESTTIME(dateadd(day, -179, @currentDate));
        else if @SHOWCODE = 9   -- 1y

            set @STARTDATE = dbo.UFN_DATETIMEOFFSET_GETEARLIESTTIME(dateadd(day, -364, @currentDate));
    end

else if @SHOWCODE = 99 -- Custom

    begin
        set @STARTDATE = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(@STARTDATE, 0)
        set @ENDDATE = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(@ENDDATE, 0);
        set @STARTTIME = nullif(@STARTTIME, '');
        set @ENDTIME = nullif(@ENDTIME, '');

        if @STARTDATE is not null
            begin
                set @STARTDATE = dbo.UFN_DATETIMEOFFSET_GETEARLIESTTIME(@STARTDATE);
                if @STARTTIME is not null
                    begin
                        set @STARTDATE = dateadd(hh, cast(substring(@STARTTIME, 1, 2) as int), @STARTDATE);
                        set @STARTDATE = dateadd(mi, cast(substring(@STARTTIME, 3, 2) as int), @STARTDATE);                        
                    end
            end

        if @ENDDATE is not null
            begin
                if @ENDTIME is null
                    set @ENDDATE = dbo.UFN_DATETIMEOFFSET_GETLATESTTIME(@ENDDATE);

                else
                    begin
                        set @ENDDATE = dbo.UFN_DATETIMEOFFSET_GETEARLIESTTIME(@ENDDATE);            
                        set @ENDDATE = dateadd(hh, cast(substring(@ENDTIME, 1, 2) as int), @ENDDATE);
                        set @ENDDATE = dateadd(mi, cast(substring(@ENDTIME, 3, 2) as int), @ENDDATE);
                        set @ENDDATE = dateadd(s, 59, @ENDDATE);

                        -- Push the end date to the latest valid SQL Server millisecond.

                        set @ENDDATE = dateadd(ms, 997, @ENDDATE);
                    end
            end

    end

return 0;