Combine two date time stamps with date from first and time from second

Question

I have a table that stores the start date and end date of appointment in two different columns. They are both date times. Its for easy importation of the old data. I cant change the structure. I am using Microsoft SQL Server.

ApptDate                   ApptTime
2012-02-16 00:00:00.000    1899-12-30 11:45:00.000
2012-02-16 00:00:00.000    1899-12-30 13:15:00.000
2012-02-16 00:00:00.000    1899-12-30 13:30:00.000

What I need to do is combine these two date times in one. I am calling a stored proc. My question is what would be my best way of doing this?

ALTER PROCEDURE [dbo].[GetTodaysAppointsNonRepeating] 
    -- Add the parameters for the stored procedure here
    @DateToday DATETIME, @ClientID INT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT a.ID, a.ApptDate, a.ApptTime, a.ApptLength, a.checkedin, a.Entered, a.UserID, a.ProviderID, a.PatientID, CASE WHEN patient.FirstName is null then a.Name END as name, 
    CASE WHEN HomePhone !='' then HomePhone WHEN WorkPhone !='' THEN WorkPhone ELSE CellPhone END as Telephone
    FROM Appointments as a INNER JOIN Patient on a.PatientID = patient.id and 
    a.clientid = patient.clientid WHERE ApptDate = @DateToday and a.ClientID = @ClientID
    AND RepeatType = 'N'
END

Possible duplicate of [Combining (concatenating) date and time into a datetime](https://stackoverflow.com/questions/18622384/combining-concatenating-date-and-time-into-a-datetime). Check out the selected answer from Aaron Bertrand. — Chris Albert, Dec 19 '17 at 21:08

score 0 · Accepted Answer · answered Dec 19 '17 at 21:11

0

This is how you would combine the two datetime fields together:

SELECT DATETIMEFROMPARTS(YEAR(ApptDate)
                        ,MONTH(ApptDate)
                        ,DAY(ApptDate)
                        ,DATEPART(HOUR,ApptTime)
                        ,DATEPART(MINUTE,ApptTime)
                        ,DATEPART(SECOND,ApptTime)
                        ,DATEPART(MILLISECOND,ApptTime))

answered Dec 19 '17 at 21:11

digital.aaron

5,435
2
24
43

Or, as we're apparently assuming T-SQL, even more directly? `DATEADD(millisecond, DATEDIFF(millisecond, '1899-12-30', ApptTime), ApptDate)`? – MatBailie Dec 19 '17 at 21:13

Combine two date time stamps with date from first and time from second

1 Answers1