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)  order by question

Author  Topic  hmusa Starting Member 36 Posts Posted - 2001-08-29 : 07:20:25 I have the following query that sorts out news stories by date and time it is correctly sorting by date but the times are not, it seems that the order by time clause is not working Any Ideas? Thanxs in advance DROP PROC spNewsTEST go CREATE PROC spNewsTEST @PPNT VARCHAR(2)='NT', @ID VARCHAR(6)= '%', @SearchText VARCHAR(255) = '' as BEGIN DECLARE @DateFilter DATETIME -- @PPNT is a filter on the source of the data -- we need a date filter when no ID is passed. We can give each date range a code. -- DateRangeCode 1 - today -- DateRangeCode 2 - Last week -- DateRangeCode 3 - Last month SELECT @DateFilter = '01 jan 1900' if @ID = '%' BEGIN SELECT @DateFilter = DATEadd(m,-1 ,GETDATE() ) END IF @SearchText = '' BEGIN select sourceCode, CONVERT(VARCHAR(11),CONVERT(smallDATETIME,[date],103) ) as Date,LTRIM(substring(convert(VARCHAR(25),[time]),13,7)) as time, id,headline,NewsID, CASE WHEN DATEDIFF(dd, date,GETDATE() ) <1 THEN 1 WHEN DATEDIFF(dd, date,GETDATE() ) BETWEEN 1 AND 7 THEN 2 WHEN DATEDIFF(dd, date,GETDATE() ) BETWEEN 8 AND 31 THEN 3 ELSE 4 END as DateRangeCode, Pending, Announcement, LastModifiedDate,LastmodifiedUser from news (NOLOCK) where id like @ID AND SourceCode = @PPNT AND Date >= @DateFilter ORDER BY dateRangeCode, convert(datetime,[date]) desc,[time] DESC END -- for search text use this query IF @SearchText <> '' BEGIN select sourceCode, CONVERT(VARCHAR(11),CONVERT(smallDATETIME,[date],103) ) as Date,LTRIM(substring(convert(VARCHAR(25),[time]),13,7)) as time, id,headline,NewsID, CASE WHEN DATEDIFF(dd, date,GETDATE() ) <1 THEN 1 WHEN DATEDIFF(dd, date,GETDATE() ) BETWEEN 1 AND 7 THEN 2 WHEN DATEDIFF(dd, date,GETDATE() ) BETWEEN 8 AND 31 THEN 3 ELSE 4 END as DateRangeCode, Pending, Announcement, LastModifiedDate,LastmodifiedUser from news (NOLOCK) where id like @ID AND SourceCode = @PPNT AND ( story like '%' + @SearchText + '%' OR headline like '%' + @SearchText + '%' OR firstline like '%' + @SearchText + '%' ) AND Date >= @DateFilter order by convert(datetime,[date]) desc,[time] desc END END go Edited by - hmusa on 08/29/2001 07:37:49