SQL Server Concat Date and Time into DateTime

In this article, I’ll walk you through everything you need to know about how to concat date and time into datetime in SQL Server. I’ll cover multiple methods, explain their pros and cons, and provide practical examples you can use right away.

SQL Server Concat Date and Time into DateTime

Before diving into the how, let’s understand the why:

• Data Storage Efficiency: Storing date and time separately can be inefficient and complicate queries.
• Simplified Queries: Combining into one datetime column simplifies filtering and calculations.
• Reporting & Analytics: Most reporting tools expect a single datetime field.
• Avoiding Errors: Combining properly avoids rounding or truncation errors.

Method 1: Using CAST and CONVERT with String Concatenation

One straightforward way is to convert both date and time to strings, concatenate them, then cast back to datetime.

SELECT
CAST(CONVERT(varchar(10), EventDate, 120) + ' ' + CONVERT(varchar(8), EventTime, 108) AS datetime) AS CombinedDateTime
FROM DateTimeSeparate;

• CONVERT(varchar(10), YourDateColumn, 120) Converts date to YYYY-MM-DD.
• CONVERT(varchar(8), YourTimeColumn, 108) converts time to HH:MM:SS.
• Concatenate with a space in between.
• Cast the concatenated string back to datetime.

After executing the above query, I got the expected output as shown in the screenshot below.

Pros:

• Simple to understand and implement.
• Works well for SQL Server 2008 and later.

Cons:

• Involves implicit conversions and string manipulation, which may affect performance on large datasets.

Method 2: Using DATEADD with DATEDIFF

This method adds the time portion as seconds (or milliseconds) to the date.

SELECT
    DATEADD(SECOND, DATEDIFF(SECOND, 0, EventTime), CAST(EventDate AS datetime)) AS CombinedDateTime
FROM DateTimeSeparate;

Explanation:

• DATEDIFF(SECOND, 0, YourTimeColumn) calculates the number of seconds since midnight.
• DATEADD(SECOND, ..., YourDateColumn) adds those seconds to the date.

After executing the above query, I got the expected output as shown in the screenshot below.

Pros:

• Avoids string manipulation.
• Efficient and precise.

Cons:

• Works best if the time precision is to the second.

For higher precision (milliseconds or microseconds), adjust the units accordingly.

Method 3: Using DATETIME2 and Arithmetic

If your columns are date and time, You can cast both to datetime2 and add:

declare @dc date = '2025-08-08'
declare @tc time(7) = '23:59:59.9999999'

select cast(concat(@dc, ' ', @tc) as datetime2(7))

After executing the above query, I got the expected output as shown in the screenshot below.

Method 4: Using DATETIMEFROMPARTS (SQL Server 2012+)

If you want to build a datetime from components explicitly:

SELECT
    DATETIMEFROMPARTS(
        YEAR(EventDate),
        MONTH(EventDate),
        DAY(EventDate),
        DATEPART(HOUR, EventTime),
        DATEPART(MINUTE, EventTime),
        DATEPART(SECOND, EventTime),
        DATEPART(MILLISECOND, EventTime)
    ) AS CombinedDateTime
FROM DateTimeSeparate;

After executing the above query, I got the expected output as shown in the screenshot below.

Pros:

• Very explicit and precise.
• Avoids any implicit conversions.

Cons:

• Verbose.
• Only available in SQL Server 2012+.

Performance Comparison of Methods

If you’re working with large datasets, performance matters. Here’s a quick overview:

Practical Example: Combining Appointment Date and Time

Let’s say you have a table called Appointments with columns:

You want to create a DateTime column that combines these.

Using Method 1 (CAST + CONVERT):

SELECT
    Appointment_date,
    Appointment_time,
    CAST(CONVERT(varchar(10), Appointment_date, 120) + ' ' + CONVERT(varchar(8), Appointment_time, 108) AS datetime) AS AppointmentDateTime
FROM Appointment_schedule;

After executing the above query, I got the expected output as shown in the screenshot below.

Using Method 2 (DATEADD + DATEDIFF):

SELECT
    AppointmentDate,
    AppointmentTime,
    DATEADD(SECOND, DATEDIFF(SECOND, 0, AppointmentTime), CAST(AppointmentDate AS datetime)) AS AppointmentDateTime
FROM Appointments;

After executing the above query, I got the expected output as shown in the screenshot below.

Tips for Working with Date and Time in SQL Server

• Always be aware of the data types you are working with.
• Use datetime2 for better precision and a wider range.
• Avoid unnecessary string conversions for performance.
• Test your queries with edge cases like midnight (00:00:00) and end of day (23:59:59.999).
• When working with time zones, consider using datetimeoffset.

Summary Table of Methods

Video Tutorial

Final Thoughts

Combining date and time into a datetime value in SQL Server is a common but sometimes tricky task. The right method depends on your SQL Server version, precision requirements, and performance considerations.

For most modern applications, It is recommended to use casting to datetime2 and adding the values for precision and simplicity. If you need compatibility with older versions or simpler syntax, string concatenation or DATEADD Methods work well.

You may also like the following articles.

• SQL Server Date Difference in Hours
• Get Financial Year from Current Date in SQL Server
• SQL Server Last Login Date for User
• SQL Server Get The Latest Record By Date
• SQL Server Date Difference in Hours

Bijay Kumar Sahoo

After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.