How to combine date from one field with time from another field - MS SQL Server

Precision matters: Including milliseconds

Don't regret missing moments. This approach includes milliseconds in your combined datetime:

SELECT 
  DATEADD(millisecond, DATEDIFF(millisecond, '00:00:00.000', timeField), CAST(dateField AS datetime2)) AS CombinedDateTime
FROM 
  YourTable;


Remember, even milliseconds can be the difference between a perfect soft-boiled egg and a disappointment!

Keeping it compatible: SQL Server 2008

For compatibility with SQL Server 2008 or earlier, use the DATETIME datatype, not DATETIME2:

SELECT 
  DATEADD(millisecond, DATEDIFF(millisecond, '00:00:00.000', timeField), CAST(dateField AS datetime)) AS CombinedDateTime
FROM 
  YourTable;


"The more you know about SQL versions, the less they scare!"

Code simplicity: Combine with + operator

Use the + operator with CAST to combine fields simply:

SELECT 
  CAST(CAST(dateField AS date) AS datetime) + CAST(timeField AS datetime) AS CombinedDateTime
FROM 
  YourTable;


It's like SQL Server 2012 took a minimalism class!

Clean organization: Use variables

Keep your query neat. Store interim values in variables:

DECLARE @DatePart DATE = '2023-03-14';
DECLARE @TimePart TIME(7) = '21:59:59.1234567';

SELECT 
  CAST(@DatePart AS DATETIME) + CAST(@TimePart AS DATETIME) AS CombinedDateTime;


It's like decluttering your room, but for your SQL script!

Written standards: Readability tips

For readability, use standardized datetime formats like ISO 8601

SELECT 
  FORMAT(CombinedDateTime, 'yyyy-MM-ddTHH:mm:ss.fff') AS StandardDateTime
FROM 
  YourTable;


TimeStamp readability: Making it burn, not burn your eyes!

Dealing with zeroes: Avoiding errors

Handle zero values in date or time columns to dodge errors:

SELECT 
  CASE 
    WHEN dateField = 0 OR timeField = 0 THEN NULL
    ELSE DATEADD(day, DATEDIFF(day, 0, dateField), CAST(timeField AS datetime))
  END AS CombinedDateTime
FROM 
  YourTable;


"Zero heroes don't let zeroes hurt their datetimes!"

Performant solution: No char conversions

Optimize performance and avoid char conversions:

SELECT 
  DATEADD(day, DATEDIFF(day, 0, dateField), timeField) as CombinedDateTime
FROM 
  YourTable;


"Performance: The difference between lightning and the time it takes to say 'lightning'."