How To Sort DateField(where datafield is stored as varchar)
9101,,3/28/2008,~/images/PlusSign.gif,~/images/PlusSign.gif,~/images/PlusSign.gif,~/images/PlusSign.gif,1,400 9102,,3/7/2008,~/images/PlusSign.gif,~/images/PlusSign.gif,~/images/PlusSign.gif,~/images/PlusSign.gif,1,400
9103,~/images/PlusSign.gif,~/images/PlusSign.gif,~/images/PlusSign.gif,~/images/PlusSign.gif,~/images/PlusSign.gif
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER proc [dbo].[usp_getsevent]
@userid int
as
create table #tempdat (scheduleeventid int, eventdate varchar(100),opponents varchar(100),locationname varchar(100),eventtime varchar(100),officials varchar(100))
create table #tempdat1 (scheduleeventid int, eventdate varchar(100),opponents varchar(100),locationname varchar(100),eventtime varchar(100),officials varchar(100))
create table #tempdat2 (scheduleeventid int, eventdate varchar(100),opponents varchar(100),locationname varchar (100),eventtime varchar(100),officials varchar(100))
declare @scheduleeventid int, @eventdate varchar(100),@opponents varchar(100),@locationname varchar(100),@eventtime varchar(100),@officials varchar(100),@INDEX varchar(10),@tdate varchar(100),@tempdate varchar(100),@tempdate1 varchar(100),@tempdate2 varchar(100)
BEGIN
DECLARE date_Cursor CURSOR FOR
SELECT scheduleEventid,eventdate,opponents,locationname,eventtime,officials FROM scheduledevent where eventdate NOT like '%~%' AND userid = @userid
OPEN date_Cursor
FETCH NEXT FROM date_Cursor
INTO @scheduleeventid,@eventdate,@opponents,@locationname,@eventtime,@officials
WHILE @@FETCH_STATUS = 0
BEGIN
SET @INDEX = CHARINDEX('-', @eventdate)
IF @INDEX >0
BEGIN
SET @tempdate = RIGHT(@eventdate, LEN(@tdate)-@INDEX)
insert into #tempdat (scheduleeventid,eventdate,opponents,locationname,eventtime,officials) values (@scheduleeventid,@eventdate,@opponents,@locationname,@eventtime,@officials)
END
ELSE
BEGIN
insert into #tempdat (scheduleeventid,eventdate,opponents,locationname,eventtime,officials) values (@scheduleeventid,@eventdate,@opponents,@locationname,@eventtime,@officials)
END
FETCH NEXT FROM date_Cursor
INTO @scheduleeventid,@eventdate,@opponents,@locationname,@eventtime,@officials
END
CLOSE date_Cursor
DEALLOCATE date_Cursor
insert into #tempdat1 select * from #tempdat order by cast(convert(varchar(10),eventdate,101)AS datetime) desc
insert into #tempdat2 SELECT scheduleEventid,eventdate,opponents,locationname,eventtime,officials FROM scheduledevent where eventdate like '%~%' And userid = @userid
insert into #tempdat1 SELECT * FROM #tempdat2
SELECT * from #tempdat1
drop table #tempdat
drop table #tempdat1
drop table #tempdat2
END
Comments