Database Design – Preferred Way to Store DateTime

Storing the data in a single column is the preferred way, since they are inextricably linked. A point in time is a single piece of information, not two.

A common way of storing date/time data, employed "behind the scenes" by many products, is by converting it into a decimal value where the "date" is the integer portion of the decimal value, and the "time" is the fractional value. So, 1900-01-01 00:00:00 is stored as 0.0 and September 20th, 2016 9:34:00 is stored as 42631.39861. 42631 is the number of days since 1900-01-01. .39861 is the portion of time elapsed since midnight. Don't use a decimal type directly to do this, use an explicit date/time type; my point here is just an illustration.

Storing the data in two separate columns means you'll need to combine both column values any time you want to see if a given point in time is earlier or later than the stored value.

If you store the values separately, you'll invariably run into "bugs" that are difficult to detect. Take for instance the following:

IF OBJECT_ID('tempdb..#DT') IS NOT NULL
DROP TABLE #DT;
CREATE TABLE #DT
(
    dt_value DATETIME NOT NULL
    , d_value DATE NOT NULL
    , t_value TIME(0) NOT NULL
);


DECLARE @d DATETIME = '2016-09-20 09:34:00';

INSERT INTO #DT (dt_value, d_value, t_value)
SELECT @d, CONVERT(DATE, @d), CONVERT(TIME(0), @d);

SET @d = '2016-09-20 11:34:00';

INSERT INTO #DT (dt_value, d_value, t_value)
SELECT @d, CONVERT(DATE, @d), CONVERT(TIME(0), @d);

/* show all rows with a date after 2016-07-01 11:00 am */
SELECT *
FROM #DT dt
WHERE dt.dt_value >= '2016-07-01 11:00:00';

/* show all rows with a date after 2016-07-01 11:00 am */
SELECT *
FROM #DT dt
WHERE dt.d_value >= CONVERT(DATE, '2016-07-01')
    AND dt.t_value >= CONVERT(TIME(0), '11:00:00');

In the above code, we're creating a test table, populating it with two values, then performing a simple query against that data. The first SELECT returns both rows, however the second SELECT only returns a single row, which may not be the desired outcome:

The correct way to filter a date/time range where the values are in discrete columns, as pointed out by @ypercube in comments, is:

WHERE dt.d_value > CONVERT(DATE, '2016-07-01') /* note there is no time component here */
    OR (
        dt.d_value = CONVERT(DATE, '2016-07-01') 
        AND dt.t_value >= CONVERT(TIME(0), '11:00:00')
    )

If you need the time component separated for analysis purposes, you could consider adding a calculated, persisted, column for the time portion of the value:

ALTER TABLE #DT
ADD dt_value_time AS CONVERT(TIME(0), dt_value) PERSISTED;

SELECT *
FROM #dt;

The persisted column could then be indexed allowing for fast sorts, etc, by time-of-day.

If you are considering splitting the date and time into two fields for display purposes, you should realize that formatting should be done at the client, not the server.