Add Date parameter and time parameter to single date time

I'm modifying a store procedure in SQL Server 2008. The original procedure took in two dates

@StartDate DATETIME
@EndDate   DATETIME

And would convert the time portion to the Earliest and latest possible times respectively.

I have added two additional parameters to accept Time Portions.

@StartTime DATETIME
@EndT开发者_运维百科ime   DATETIME

The Time portions are optional.

An RDL report file generates the report online for the users. The logic needs to occur in the Stored Proc.

What I have so far is not much, as I'm a C# programmer leaving my element.

IF (@StartTime IS NULL)
    SET @StartDate = fn_SetBeginningTime(@StartDate) -- Sets time portion to earliest value 
ELSE
    -- This is where I don't know how to add the time from @StartTime to the time portion of the datetime value @StartDate
IF (@EndTime IS NULL)
    -- This will do the same as the start time/start date

---

Assuming:

1. By earliest you mean 00:00:00 on the start date
2. By latest you mean 00:00:00 on the day after enddate (for use with <=)

Should there be any, this will ignore the time from a @*Date param and the date from a@*Time param.

declare @StartDate DATETIME = '15 jul 2010'
declare @EndDate   DATETIME = '15 jul 2010'
declare @StartTime DATETIME = '06:06:06'
declare @EndTime   DATETIME = null

--always make @StartDate/@EndDate's time 00:00:00
SET @StartDate = CAST(@StartDate AS DATE)
SET @EndDate =   CAST(@EndDate AS DATE)

IF (@StartTime IS NOT NULL)                -- set @StartDate's time to @StartTime
    SET @StartDate += CAST(@StartTime AS TIME)

IF (@EndTime IS NULL)
    SET @EndDate += 1                      --set it to midnight
ELSE
    SET @EndDate += CAST(@EndTime AS TIME) --set @EndDate's time to @EndTime

select @StartDate, @EndDate

>>> 2010-07-15 06:06:06.000,2010-07-16 00:00:00.000

For DATE / TIME;

declare @StartDate DATE = '15 jul 2010'
declare @EndDate   DATE = '15 jul 2010'
declare @StartTime TIME = null
declare @EndTime   TIME = '22:22:22'

declare @start datetime = cast(@StartDate as datetime) + coalesce(@StartTime, cast('00:00:00' as time))
declare @end   datetime = cast(@EndDate   as datetime) + coalesce(@EndTime,   cast('23:59:59' as time))

select @start, @end