Combining Date and Time Fields to DateTime, SQL Server 2008

sql

date-time

sql-server

data-types

byNikita Barsukov·Feb 2, 2025

Rather than converting Date and Time to strings, use SQL Server's optimized functions:

SELECT DATEADD(day, DATEDIFF(day, 0, DateField), CAST(TimeField AS DATETIME)) AS DateTimeCombined
FROM YourTable;

This feats of speed courtesy of DATEADD and DATEDIFF, efficiently combines Date and Time into a DateTime value, skipping unnecessary conversions.

Efficient Date and Time Combining

The magic trick in SQL Server for combining Date and Time fields isn't actually pulling a rabbit out of a hat. Here are your magician's tools:

Swerve string conversions, they're like rabbit holes, leading to inefficient queries and possible formatting issues.
SQL Server's in-built functions like DATEADD and DATEDIFF are your magic wands, optimized for handling Date and Time data manipulation without waving sparkles in your eyes.
DateTime data types are like magic boxes, out of which both date and time pop-up simultaneously when combined.

Casting Call: When and Why?

When to cast

Type mismatch: If your Date and Time fields are not DateTime-compatible, bring on the magic of casting.
Precision affair: Retain every fine detail of time (even milliseconds) using casting.

Not the time for casting

Time arithmetic: Rely on the mighty DATEADD, your trusty magic wand.
Part extraction: DATEPART is preferable to casting a spell and parsing.

Common Issues: The Vanishing Act

What to watch for when performing this magic:

Data disappearing: Improper casting might lead to data loss, like a magic trick gone wrong.
Performance dips: Too many unnecessary casting spells can slow down the show.
Complicated routine: Over-complicated methods can confuse the audience (and you).

How To Polish Your Magic Trick

Practice makes perfect. Fine-tune these strategies:

Simplify your magic spell

Condense your SQL with the shortest syntax that still pulls the rabbit out of the hat:

SELECT CAST(DateField AS DATETIME) + CAST(TimeField AS DATETIME) AS DateTimeResult
FROM YourTable;
-- Way more straightforward, right? No rabbits harmed 🐇