Bad Date 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 2005 Forums

Transact-SQL (2005)

Bad Date cursor

Author

Topic

jbosco1988
Starting Member

46 Posts

Posted - 2009-10-29 : 14:24:31

Hello, I am trying to create a Cursor to check for a Bad Date in a table. Something like as follows:

TABLE NAME: UB.CLAIMBAK
COLUMN NAME: P_BDATE

Something like this:

BEGIN

DECLARE @P_BDATE VARCHAR (20)
DECLARE @T_DATE DATETIME

BEGIN TRY

SELECT @T_DATE = CAST(P_BDATE AS DATETIME) FROM UB.CLAIMBAK
END TRY

BEGIN CATCH
PRINT 'BAD DATE'
END CATCH

PRINT @T_DATE

END

My main question is how do I get the Cursor to look in the UB.CLAIMBAK table. I am really new to Cursors so any help would be greatly appreciated.

Jim

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-29 : 14:29:09

SELECT * from CLAIMBAK
WHERE ISDATE(P_BDATE) =

will give you values that SQL can't convert to a date.

Jim

Everyday I learn something that somebody else already knew

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-10-29 : 14:47:05

what is the unique indentity field of UB.CLAIMBAK table?


declare @BadDate varchar(50)
declare @CLAIMBAKID INT


DECLARE Brazil_Will_win CURSOR FOR
SELECT P_BDATE, CLAIMBAKID
  FROM UB.CLAIMBAK;

OPEN Brazil_Will_win

FETCH NEXT FROM Brazil_Will_win INTO @BadDate, @CLAIMBAKID
WHILE @@FETCH_STATUS = 0
	BEGIN
		IF ISDATE(@BadDate)
			BEGIN
				PRINT @CLAIMBAKID
			END
			FETCH NEXT FROM Brazil_Will_win INTO @BadDate, @CLAIMBAKID
	END
CLOSE Brazil_Will_win
DEALLOCATE Brazil_Will_win
GO

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

jbosco1988
Starting Member

46 Posts

Posted - 2009-10-29 : 17:50:53

Thanks to both you guys, That worked out great Yosiasz!!!!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-10-29 : 17:51:28

quote:
Originally posted by jimf

SELECT * from CLAIMBAK
WHERE ISDATE(P_BDATE) =

This is missing something:

SELECT * from CLAIMBAK
WHERE ISDATE(P_BDATE) = 0

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-30 : 02:57:25

However, ISDATE() is not reliable

SELECT ISDATE(2000),ISDATE('2000'),ISDATE(200008/100)

You need to check the length of the VARCHAR data along with ISDATE()

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources