Cursor_sql server 2005
-
hi, I am learning stage of cursor.here date format is displaying like 2007-06-22 01:54:02.000. I am Struggling for getting normal date format like 11/12/2005...Plz See the following Code and reply me ALTER procedure [dbo].[WCReport] as begin create table #temp21 ( Ason datetime ,SubScriberEmail varchar(50),SvcActivationDate datetime, PkgActivationDate datetime,RenewalDate datetime ) DECLARE @cursorVar CURSOR SET @cursorVar = CURSOR Forward_Only FOR SELECT regdate,email,wct.dt from wc_transaction as wct,wc_user as wcu WHERE wct.PID=wcu.ID --open the cursor OPEN @cursorVar DECLARE @regdate datetime, @email varchar(50),@dt datetime --fetches the current row, which is the first FETCH FROM @cursorVar INTO @regdate,@email,@dt WHILE @@fetch_status = 0 BEGIN insert into #TEMP21(Ason,SubscriberEmail,SvcActivationDate,PkgActivationDate,RenewalDate) VALUES(@regdate,@email,@regdate,@regdate,@dt) FETCH Next FROM @cursorVar INTO @regdate,@email,@dt END begin select * from #temp21 return end CLOSE @cursorVar DEALLOCATE @cursorVar end exec WCReport
Rajendran
-
hi, I am learning stage of cursor.here date format is displaying like 2007-06-22 01:54:02.000. I am Struggling for getting normal date format like 11/12/2005...Plz See the following Code and reply me ALTER procedure [dbo].[WCReport] as begin create table #temp21 ( Ason datetime ,SubScriberEmail varchar(50),SvcActivationDate datetime, PkgActivationDate datetime,RenewalDate datetime ) DECLARE @cursorVar CURSOR SET @cursorVar = CURSOR Forward_Only FOR SELECT regdate,email,wct.dt from wc_transaction as wct,wc_user as wcu WHERE wct.PID=wcu.ID --open the cursor OPEN @cursorVar DECLARE @regdate datetime, @email varchar(50),@dt datetime --fetches the current row, which is the first FETCH FROM @cursorVar INTO @regdate,@email,@dt WHILE @@fetch_status = 0 BEGIN insert into #TEMP21(Ason,SubscriberEmail,SvcActivationDate,PkgActivationDate,RenewalDate) VALUES(@regdate,@email,@regdate,@regdate,@dt) FETCH Next FROM @cursorVar INTO @regdate,@email,@dt END begin select * from #temp21 return end CLOSE @cursorVar DEALLOCATE @cursorVar end exec WCReport
Rajendran
Instead of using select * from #temp21 specify the each column name and in the date column use convert function. now your select query will be like this.
select convert(varchar,Ason,101),SubScriberEmailfrom, convert(varchar,SvcActivationDate,101),convert(varchar,PkgActivationDate,101), convert(varchar,RenewalDate,101) from #temp21 -
Instead of using select * from #temp21 specify the each column name and in the date column use convert function. now your select query will be like this.
select convert(varchar,Ason,101),SubScriberEmailfrom, convert(varchar,SvcActivationDate,101),convert(varchar,PkgActivationDate,101), convert(varchar,RenewalDate,101) from #temp21Hi, I have used your query Its working fine....Thanks alot. I have another doubt....actually I am generating reports using this cursor.here I have to give dublicate column name like Status(if condition is true)..So I want to know how to provide an alias name for that Status(Status is not their in Database)....If u know plz reply me... thanks
Rajendran
-
Hi, I have used your query Its working fine....Thanks alot. I have another doubt....actually I am generating reports using this cursor.here I have to give dublicate column name like Status(if condition is true)..So I want to know how to provide an alias name for that Status(Status is not their in Database)....If u know plz reply me... thanks
Rajendran
Hi Try something like SELECT CASE WHEN Dt < '2006-12-31' THEN 'Archived' WHEN Dt < '2007-06-30' THEN 'OLD' ELSE 'New' END AS [Status] FROM MyTable
Nav
-
Hi Try something like SELECT CASE WHEN Dt < '2006-12-31' THEN 'Archived' WHEN Dt < '2007-06-30' THEN 'OLD' ELSE 'New' END AS [Status] FROM MyTable
Nav