Stored Procedure / Query required

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Stored Procedure / Query required

Author

Topic

ashy_16in
Starting Member

16 Posts

Posted - 2004-02-05 : 10:58:56

I need to sort records in a table based on the current time. For example, if the current time is 9-00am then all the records between 9-00am and 10-00am ( in the starttime column) have to be displayed on top followed by the other records. If the current time is 10-15am then all the records between 10-00am and 11-00am have to be displayed on top followed by other records, in other words the records have to be sorted every hour based on the current time. The columns in the table are itemid, itemname and starttime .

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-02-05 : 11:36:23

Determine the upper and lower bounds for the hour something like this:

DECLARE @StartHr DATETIME, @EndHr DATETIME
SELECT @StartHr = CONVERT(datetime, CONVERT(VARCHAR(8), GETDATE(), 112) + ' ' + CONVERT(VARCHAR(2), DATEPART(hh, GETDATE())) + ':00:00')
SELECT @EndHr = CONVERT(datetime, CONVERT(VARCHAR(8), GETDATE(), 112) + ' ' + CONVERT(VARCHAR(2), DATEPART(hh, GETDATE())) + ':59:59')

Then create a select statement with the datetime column between the start hour and end hour, UNION the rest similar to this:

SELECT 1, * FROM @Table WHERE timestamp BETWEEN @StartHr AND @EndHr
UNION
SELECT 2, * FROM @table WHERE timestamp < @StartHr OR timestamp > @EndHr
ORDER BY 1, 2

Raymond

stephe40
Posting Yak Master

218 Posts

Posted - 2004-02-05 : 11:37:47

Your going to have to be more specific, maybe provide some DDL.

Do you want records that are within 1 hour of the current time? If so thats a filter condition that needs to go in the where clause. Somthing like:

where datediff(mi, mytimecolumn, getdate()) is between -60 and 60

- Eric

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-02-05 : 11:41:36

Eric

What was asked for was the records to be sorted every hour, 10:15 to fall in a group containing 10:00 to 11:00 etc.

Raymond

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources