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