Replace SQL column values

I just use the DATEADD/DATEDIFF pattern twice:

declare @t table (Appdate datetime,begintime datetime,endtime datetime)
insert into @t(Appdate,begintime,endtime) values
('2013-09-11T00:00:00.000','1900-01-01T12:30:00.000','1900-01-01T14:45:00.000')

select Appdate,
    DATEADD(day,DATEDIFF(day,begintime,AppDate),beginTime) as AdjustedBegin,
    DATEADD(day,DATEDIFF(day,endtime,AppDate),endtime) as AdjustedEnd
from @t

This avoids mucking about with splitting out separate components or converting to/from strings.

The inner DATEDIFF(day,begintime,AppDate) just computes the number of midnight transitions between begintime and AppDate - it doesn't pay any heed to the time components of either, and it returns a whole number. If we add (DATEADD) that number of days onto begintime, we logically get a datetime value from the same date as AppDate but with it's time components unchanged from begintime.

DECLARE @t table (
   appdate   datetime
 , begintime datetime
 , endtime   datetime
);

INSERT INTO @t (appdate, begintime, endtime)
  VALUES ('2013-09-11 00:00:00.000', '1900-01-01 12:30:00.000', '1900-01-01 14:45:00.000')
;

SELECT appdate
     , begintime
     , DateAdd(mi, begintime_mi, DateAdd(hh, begintime_hh, appdate)) As new_begintime
     , endtime
     , DateAdd(mi, endtime_mi, DateAdd(hh, endtime_hh, appdate)) As new_endtime
FROM   (
        SELECT appdate
             , begintime
             , DatePart(hh, begintime) As begintime_hh
             , DatePart(mi, begintime) As begintime_mi
             , endtime
             , DatePart(hh, endtime) As endtime_hh
             , DatePart(mi, endtime) As endtime_mi
        FROM   @t
       ) As finding_time_portions
;

This grabs the portions of time from begintime and endtime using the DatePart() function and then adds them on to the original appdate using the DateAdd() function.

is it not as simple as this

DECLARE @t TABLE(Appdate DATETIME,begintime DATETIME,endtime DATETIME)
INSERT INTO @t  
VALUES
('2013-09-11 00:00:00.000','1900-01-01 12:30:00.000','1900-01-01 14:45:00.000')

SELECT * FROM @t

╔═════════════════════════╦═════════════════════════╦═════════════════════════╗
║         Appdate         ║        begintime        ║         endtime         ║
╠═════════════════════════╬═════════════════════════╬═════════════════════════╣
║ 2013-09-11 00:00:00.000 ║ 1900-01-01 12:30:00.000 ║ 1900-01-01 14:45:00.000 ║
╚═════════════════════════╩═════════════════════════╩═════════════════════════╝

Query

SELECT   Appdate 
       , begintime + Appdate AS  begintime
       , endtime + Appdate   AS  endtime
FROM @t

Result Set

╔═════════════════════════╦═════════════════════════╦═════════════════════════╗
║         Appdate         ║        begintime        ║         endtime         ║
╠═════════════════════════╬═════════════════════════╬═════════════════════════╣
║ 2013-09-11 00:00:00.000 ║ 2013-09-11 12:30:00.000 ║ 2013-09-11 14:45:00.000 ║
╚═════════════════════════╩═════════════════════════╩═════════════════════════╝

TRY this...

select a.Appdate,
 convert(datetime,(convert(varchar(10),convert(date,a.Appdate))+' ' + convert(varchar(10),convert(time,a.begintime)))) begintime,
convert(datetime,(convert(varchar(10),convert(date,a.Appdate))+' ' + convert(varchar(10),convert(time,a.endtime)))) endtime
from tblTimetable a

Use DATEPART function to fetch the each component of the datetime. for Example:

DECLARE @datetime DATETIME
SET @datetime = GETDATE()
SELECT DATEPART(YEAR, @datetime), DATEPART(MONTH, @datetime), DATEPART(DAY, @datetime),  DATEPART(HOUR, @datetime), DATEPART(MINUTE, @datetime), DATEPART(SECOND, @datetime), DATEPART(MILLISECOND, @datetime)

In this way, you can extract each component and then form the dates in the desired format.

You can create your own function to get the DATE part only. Check the below function:

CREATE FUNCTION dbo.GetDateOnly
(
@Datetime DATETIME
)
RETURNS DATE
AS BEGIN

DECLARE @dateString VARCHAR(10), @temp VARCHAR(3)
SET @dateString = CAST(DATEPART(YEAR, @Datetime) AS VARCHAR)
SET @temp = CAST(DATEPART(MONTH, @Datetime) AS VARCHAR)
SET @dateString = @dateString + '-' + REPLICATE('0', 2 - len(@temp)) + @temp
SET @temp = CAST(DATEPART(DAY, @Datetime) AS VARCHAR)
SET @dateString = @dateString + '-' + REPLICATE('0', 2 - len(@temp)) + @temp
RETURN CONVERT(DATE, @dateString)
END
GO

Sample code to test this new function:

SELECT GETDATE()
SELECT dbo.GetDateOnly(GETDATE())

In this way, you can write your function for time also!

Just add these values :) You could cast time part to TIME datatype, but it does work without casting either.