Issue w/ Cursor - SQL Server Forums

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

Transact-SQL (2000)

Issue w/ Cursor

Author

Topic

Compkitty
Starting Member

24 Posts

Posted - 2005-05-25 : 14:43:39

I am totally having issues w/ this cursor... the code is working, but it's only pulling & converting 1/2 the data... the col002 is the PK; and then the other two are varchars that are mix of Date(s) and symbols so I pull out the Date and conver then I do a DATEDIFF...

Please tell me if it's my code..

THANKS

DECLARE @Col002 as varchar (10),@Col048 as varchar(25), @Col051 as varchar (25)

DECLARE Col_002_Cursor_Trial CURSOR FOR
SELECT Col002,Col048,Col051 FROM CDR_Create
ORDER BY Col002
OPEN Col_002_Cursor_Trial
FETCH NEXT FROM Col_002_Cursor_Trial
INTO @Col002, @Col048, @col051
WHILE @@FETCH_STATUS = 0
BEGIN

FETCH NEXT FROM Col_002_Cursor_Trial
INTO @Col002, @Col048, @col051

--Takes Date1_Complete_Time_Stamp and substrings then casts into date time...
DECLARE @CreateDateDiff as varchar(20)
SET @CreateDateDiff = @col048
DECLARE @CreateYear as Varchar(4)
SET @CreateYear = SUBSTRING(@CreateDateDiff, 1, 4)
DECLARE @CreateMonth as varchar (2)
SET @CreateMonth = SUBSTRING(@CreateDateDiff, 6,2)
DECLARE @CreateDay As varchar (2)
SET @CreateDay = SUBSTRING(@CreateDateDiff, 9,2)
DECLARE @CreateTime as varchar (8)
SET @CreateTime = SUBSTRING(@CreateDateDiff, 12, 8)

DECLARE @Date1Create as Varchar(20)
SET @Date1Create = @CreateMonth + '-' + @CreateDay +'-'+ @CreateYear + ' '+ @CreateTime
DECLARE @CreateFinal as datetime
SET @CreateFinal = CAST(@Date1Create AS datetime)

--Takes Date1_Call _Time_Stamp and substrings then cast as DateTime
DECLARE @ReleaseDateDiff as varchar(20)
SET @ReleaseDateDiff = @col051
DECLARE @ReleaseYear as Varchar(4)
SET @ReleaseYear = SUBSTRING(@ReleaseDateDiff, 1, 4)
DECLARE @ReleaseMonth as varchar (2)
SET @ReleaseMonth = SUBSTRING(@ReleaseDateDiff, 6,2)
DECLARE @ReleaseDay As varchar (2)
SET @ReleaseDay = SUBSTRING(@ReleaseDateDiff, 9,2)
DECLARE @ReleaseTime as varchar (8)
SET @ReleaseTime = SUBSTRING(@ReleaseDateDiff, 12, 8)

DECLARE @Date1Release as Varchar(20)
SET @Date1Release = @ReleaseMonth + '-' + @ReleaseDay + '-' + @ReleaseYear + ' ' + @ReleaseTime
DECLARE @ReleaseFinal as datetime
SET @ReleaseFinal = CAST(@Date1Release AS datetime)

--Finds The Difference between the two; down to the second..
DECLARE @Diff as int
SET @Diff = DATEDIFF(ss, @CreateFinal, @ReleaseFinal)

INSERT INTO TmpDate1(PKey, Date1_Create, Date1_Release, Date1_Seconds)
VALUES(@Col002, @CreateFinal, @ReleaseFinal, @Diff)
FETCH NEXT FROM Col_002_Cursor_Trial
INTO @Col002, @Col048, @col051

END
close Col_002_Cursor_Trial
deallocate Col_002_Cursor_Trial

Sully
Starting Member

30 Posts

Posted - 2005-05-25 : 14:54:06

First off, you have a fetch at the beginning of the loop and the same fetch at the end of the loop, so there's half your data.

Stuck in neutral in a pedal to the metal world,
Sully

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-05-25 : 14:59:47

Second of all, why are you using a cursor anyway???
Won't something like this work? And why are you storing the dateDiff value? that can be calculated as needed by the 2 dates.


insert TmpDate1 (PKey, Date1_Create, Date1_Release, Date1_Seconds)
select	
	Col002 PKey
	,convert(datetime,col048) Date1_Create
	,convert(datetime,col051) Date1_Release
	,datediff(
		second
		,convert(datetime,col048)
		,convert(datetime,col051)
		) Date1_Seconds
From	CDR_Create

Be One with the Optimizer
TG

Compkitty
Starting Member

24 Posts

Posted - 2005-05-25 : 15:27:43

Well I have it figured out... the reason I was using cursors, is because I have to do a lot of formatting (before) I even get to the datetime col..
But it works, and I appreciate all your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources