Binaryworld - Storing date & time values separately. ... [ SQL Server -> T-SQL ]

This example shows how date & time values can be stored separately using the SQL data types.

/*
    This table stores the date & time values as character strings.
    The ISO format is used for the date values & the military format
    for the time values. This will ensure correct ordering of the values too.
*/
CREATE TABLE #DateAndTime (
DateOnly char( 8 ) not null
    CHECK ( ISDATE( DateOnly ) = 1 And DateOnly LIKE REPLICATE( '[0-9]' , 8 ) ) ,
TimeOnly char( 8 ) not null
    CHECK( ISDATE( TimeOnly ) = 1 And LEN( TimeOnly ) = 8 And
        TimeOnly LIKE '[0-9][0-9]:[0-9][0-9]:[0-9][0-9]')
);

-- Sample data:
INSERT INTO #DateAndTime VALUES( '20001009' , '09:00:00' );
INSERT INTO #DateAndTime VALUES( '20001008' , '19:00:00' );
INSERT INTO #DateAndTime VALUES( '20001008' , '03:00:00' );
INSERT INTO #DateAndTime VALUES( CONVERT( varchar, CURRENT_TIMESTAMP , 112 ) , '18:00:00' );

SELECT * FROM #DateAndTime
ORDER BY DateOnly , TimeOnly;
/*
DateOnly TimeOnly
-------- --------
20001008 03:00:00
20001008 19:00:00
20001009 09:00:00
20001009 18:00:00
*/
GO
SELECT CONVERT( datetime , DateOnly ) AS DateVal ,
    CONVERT( datetime , TimeOnly ) AS TimeVal
FROM #DateAndTime
ORDER BY DateVal , TimeVal
/*
DateVal         TimeVal
----------------------- -----------------------
2000-10-08 00:00:00.000 1900-01-01 03:00:00.000
2000-10-08 00:00:00.000 1900-01-01 19:00:00.000
2000-10-09 00:00:00.000 1900-01-01 09:00:00.000
2000-10-09 00:00:00.000 1900-01-01 18:00:00.000
*/

-- Get only rows between 9a-10a:
SELECT * FROM #DateAndTime
WHERE DATEDIFF( hh , TimeOnly , '10:00:00' ) = 1
ORDER BY DateOnly , TimeOnly
/*
DateOnly TimeOnly
-------- --------
20001009 09:00:00
*/

-- Get only rows with today' date:
SELECT * FROM #DateAndTime
WHERE DateOnly LIKE CONVERT( varchar , CURRENT_TIMESTAMP , 112 )
/*
DateOnly TimeOnly
-------- --------
20001009 09:00:00
20001009 18:00:00
*/

-- Get only rows with today' date. This one uses the DATEDIFF function
-- directly on the dateonly column.
SELECT * FROM #DateAndTime
WHERE DATEDIFF( dd , DateOnly , CURRENT_TIMESTAMP ) = 0
/*
DateOnly TimeOnly
-------- --------
20001009 09:00:00
20001009 18:00:00
*/
GO
DROP TABLE #DateAndTime;
GO