Cursor question - 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)

Cursor question

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-09-25 : 06:29:13

I need to identify incorrect dates in a table created from a CSV file conversion.

My following script was designed to print out the variable @mortgage until the CAST fails, thereby pointing me towards the offending record.

However, the @mortgage value never changes, as I have coded something wrong somewhere.

Can someone please point me towards my blunder?

USE Pardata
GO
DECLARE @getDate CURSOR,
@mortgage nvarchar(15),
@effective_date varchar(10),
@due_Date int,
@effective_date_1 varchar(15),
@due_date_1 varchar(10),
@temp datetime

SET @getDate = CURSOR FOR

SELECT Mortgage_Number,
Effective_Date,
Due_Date,
Effective_Date_1,
Due_Date_1

FROM temp_transactions_1
OPEN @getDate
FETCH NEXT
FROM @getDate INTO @mortgage,
@effective_date,
@due_date,
@effective_date_1,
@due_date_1

WHILE (@@FETCH_STATUS = 0)

BEGIN
PRINT @mortgage
SET @temp = CAST (substring(@effective_date_1,5,2) + '/' + substring(@effective_Date_1, 7,2) + '/' + substring(@effective_Date_1,1,4) AS datetime)
END
CLOSE @getDate
DEALLOCATE @getDate
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 06:33:26

How will be format of dates in Effective_Date field?i think you can check this without use of cursor

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-25 : 06:36:19

Is it dmy or mdy?

E 12°55'05.63"
N 56°04'39.26"

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-09-25 : 06:38:28

quote:
Originally posted by Peso

Is it dmy or mdy?

E 12°55'05.63"
N 56°04'39.26"

Sorry, I should have said it is mdy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 06:58:21

quote:
Originally posted by OldMySQLUser

quote:
Originally posted by Peso

Is it dmy or mdy?

E 12°55'05.63"
N 56°04'39.26"

Sorry, I should have said it is mdy.

and why is it having datatype of varchar? you could have used datetime for holding value.

You could try below to check for invalid date values if thats what you're trying to find

SELECT Mortgage_Number,
Effective_Date,
Due_Date,
Effective_Date_1,
Due_Date_1
FROM temp_transactions_1
WHERE ISDATE(Effective_Date)=0
OR (ISDATE(Effective_Date)=1
AND LEN(Effective_Date)<8)

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-09-25 : 07:04:56

quote:
Originally posted by visakh16

quote:
Originally posted by OldMySQLUser

quote:
Originally posted by Peso

Is it dmy or mdy?

E 12°55'05.63"
N 56°04'39.26"

Sorry, I should have said it is mdy.

and why is it having datatype of varchar? you could have used datetime for holding value.

You could try below to check for invalid date values if thats what you're trying to find

SELECT Mortgage_Number,
Effective_Date,
Due_Date,
Effective_Date_1,
Due_Date_1
FROM temp_transactions_1
WHERE ISDATE(Effective_Date)=0
OR (ISDATE(Effective_Date)=1
AND LEN(Effective_Date)<8)

the dates are held as varchar in the table as this is the first was a csv file conversion and the dates need to be checked.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 07:09:13

ok. thats fine.
What according to you are invalid dates? those with invalid date values or all dates not in format yyyymmdd?

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-09-25 : 07:12:01

The dates are currently held in the table as varchar type (eg 19971112 Y-M-D). If I try asnd alter the table column to type datetime I get a conversion error, this is why I need to step through the table looking for problem(s) prior to altering the column type.

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-09-25 : 07:13:30

quote:
Originally posted by visakh16

ok. thats fine.
What according to you are invalid dates? those with invalid date values or all dates not in format yyyymmdd?

invalid dates which are not in format yyyymmdd

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 07:14:20

quote:
Originally posted by OldMySQLUser

The dates are currently held in the table as varchar type (eg 19971112 Y-M-D). If I try asnd alter the table column to type datetime I get a conversion error, this is why I need to step through the table looking for problem(s) prior to altering the column type.

then i think my earlier posted query will do it for you.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-25 : 07:31:32

quote:
Originally posted by visakh16

quote:
Originally posted by OldMySQLUser

quote:
Originally posted by Peso

Is it dmy or mdy?

E 12°55'05.63"
N 56°04'39.26"

Sorry, I should have said it is mdy.

and why is it having datatype of varchar? you could have used datetime for holding value.

You could try below to check for invalid date values if thats what you're trying to find

SELECT Mortgage_Number,
Effective_Date,
Due_Date,
Effective_Date_1,
Due_Date_1
FROM temp_transactions_1
WHERE ISDATE(Effective_Date)=0
OR (ISDATE(Effective_Date)=1
AND LEN(Effective_Date)=8)

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 07:43:10

quote:

Originally posted by madhivanan

SELECT Mortgage_Number,
Effective_Date,
Due_Date,
Effective_Date_1,
Due_Date_1
FROM temp_transactions_1
WHERE ISDATE(Effective_Date)=0
OR (ISDATE(Effective_Date)=1
AND LEN(Effective_Date)=8)

sorry Madhi
i didnt get that. shouldnt that be <8? I'm looking for invalid dates.
so either ISDATE(col)=0 or
ISDATE(col)=1 but not having day,month & year info so < 8

Madhivanan

Failing to plan is Planning to fail
[/quote]

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-09-25 : 08:03:58

Many thanks to everyone. I found my offending date (-19893456) !!!
CSV files from a third party source eh?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-25 : 08:22:02

quote:
Originally posted by visakh16

quote:
Originally posted by madhivanan

SELECT Mortgage_Number,
Effective_Date,
Due_Date,
Effective_Date_1,
Due_Date_1
FROM temp_transactions_1
WHERE ISDATE(Effective_Date)=0
OR (ISDATE(Effective_Date)=1
AND LEN(Effective_Date)=8)

sorry Madhi
i didnt get that. shouldnt that be <8? I'm looking for invalid dates.
so either ISDATE(col)=0 or
ISDATE(col)=1 but not having day,month & year info so < 8

Madhivanan

Failing to plan is Planning to fail

[/quote]
Then it is fine

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 08:24:01

quote:
Originally posted by madhivanan

Then it is fine

Madhivanan

Failing to plan is Planning to fail

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources