June 10, 2004 at 10:03 am
hi all
i have a cursor that is populated with before and after dates
whilst looping through the cursor i want to compare date a with date b and if they are different then print out a message.
the problem i have is that the dataset the cursor is built on will populate some of the dates with null values. what then seems to happen is that the comparison just doesn't fire at all
e.g
if isnull(@acceda,'1900-01-01') != isnull(@accedb,'1900-01-01') print 'account holder end date changed from '+convert(varchar(10), @accedb, 103)+' to '+convert(varchar(10), @acceda, 103)
if @acceda is null in the dataset then the comparison does not work
if i put the isnull in the select that builds the cursor then it does fire but i end up with 'account holder end date has changed from 01-01-1900 to 07-08-1968' which means i have to parse it further as 01-01-1900 is meaningless.
any ideas ?
cheers
dbgeezer
June 10, 2004 at 10:24 am
hello,
your problem lies with your string concant. any time a null is added to a string it yields null. to that end:
Declare @date as smalldatetime
Print 'this is the date '+ convert(varchar(10),@date,103)
results in nothing. While
Declare @date as smalldatetime
set @date='1/1/2001'
Print 'this is the date '+ convert(varchar(10),@date,103)
results in "this is the date 01/01/2001"
you need to isnull your @date inside the convert
HTH
Tal McMahon
June 11, 2004 at 2:22 am
hmmm
fixed it by doing an isnull(convert ... in the select
cheers
dbgeezer
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply