/*
        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

This page was last updated on May 01, 2006 04:28 PM.