How to add time to DateTime in SQL

The Next Level: Finer Control Over Time

Calculating Time Differences with DATEDIFF

To work out the elapsed time between two DateTime values before updating a DateTime, bring out the DATEDIFF function:

SELECT 
    OriginalDate, 
    TargetDate, 
    DATEDIFF(minute, OriginalDate, TargetDate) AS MinutesDifference 
FROM YourTable;
-- I always knew time travel would come in handy!


Casting: Change DateTime Type to Increase Precision

Sometimes, it's all about the date, and time is just a third wheel. To uninvite time to the party and keep only the date:

SELECT 
    CAST(YourDateTimeColumn AS DATE) AS JustTheDate 
FROM YourTable;
-- It's not you, Time. It's me.


Flexibility with Variable Time Intervals

If you're dealing with ever-changing time additions, a time variable is your ally:

DECLARE @ExtraTime TIME = '03:30:00'; -- 3 hours and 30 minutes
UPDATE YourTable
SET YourDateTimeColumn = DATEADD(second, DATEDIFF(second, 0, @ExtraTime), YourDateTimeColumn);
-- @ExtraTime, will you be my +1?


Merge Separate Dates and Times with CAST

Combine a date and a separate time to form the perfect DateTime duo with CAST:

DECLARE @DateOnly DATE = '2021-01-01';
DECLARE @TimeOnly TIME = '12:00:00';
SELECT 
    CAST(@DateOnly AS DATETIME) + CAST(@TimeOnly AS DATETIME) AS CombinedDateTime;
-- It's a date...and time!


Advanced Techniques & Things to Keep in Mind

The Peculiarities of Different SQL Versions

Don't forget that SQL syntax varies across versions like fashion trends. Something that's vogue in SQL Server 2008 R2 might be faux pas in the latest editions.

Efficiency vs Readability

While shorthand methods save time, like a trusted old pair of jeans, always choose readability for its never-out-of-style comfort and simplicity.

SQL Server Doesn't Always Make It Easy

In SQL Server 2008 R2, there is no shorthand for combining a date with a custom time. This is why we use CAST or CONVERT.

Precision Matters

To operate exclusively on the date part and ignore the time:

SELECT 
    DATEADD(day, DATEDIFF(day, 0, YourDateTimeColumn), 0) AS DateWithoutTime
FROM YourTable;
-- Sorry Time, I'm just not that into you.


When we use DATEDIFF and DATEADD together, we focus purely on the date, giving us near surgical precision.