November 5, 2003 at 5:50 am
Hi,
I have a procedure to calculate the number of work days in a period of time (don't count saturdays and sundays).
In SQL Server 6.5 it worked fine, but now in SQL 2000 it is not working. The problem is that when select count(*) returns a 0 (Zero), the if changes this value to null, and then compare null with > 0. I tried to use SET ANSI_NULLS on and off, but without success. The server was migrated from 6.5, and the compatibility level is set to 6.5.
What worries me is that this situation could be happening on other procedures. I can easily replace this procedure, but I rather to discover what's happening.
Any suggestions ?
set nocount on
declare @i int
set @i = 0
while @i < 10
begin
--print @i
if ( select datepart(weekday,getdate()+@i) where datepart(weekday,getdate()+@i) not in (1,7) ) > 0 -- is not null
begin
print 'a'
end
else
begin
print 'b'
end
set @i = @i + 1
end
November 5, 2003 at 7:51 am
Keep in mind if testing in QA it has it's own connection parameters Tools-Options->Connection Properties which override all settings in SQL Server when attaching. Check there and make sure is what you expect.
November 5, 2003 at 9:30 am
Thanks for your reply.
This code was extracted from the procedure, and shows what is happening.
What worries me is if a execute the select count(*) outside the 'IF', is returns 0 or 1 depending on the day of week, and this is the behavior that I'm expecting.
But, if I put the 'SELECT' inside 'IF', the 'IF' somehow changes the result retrieved from the SELECT COUNT(*) into a NULL value, becoming "IF (NULL) > 0".
If I test "if ( select count(*) where datepart(weekday,getdate()+@i) not in (1,7) ) is not null", it works correctly, but, according to BOL, count(*) always returns int, and in this case I'm testing against a null value.
Resuming:
a) select count(*) where datepart(weekday,getdate()) not in (1,7) returns 0 or 1 depending on the day of the week.
b) if (select count(*) where datepart(weekday,getdate()) not in (1,7) ) > 0 doesn't work, because if compares a null with 0.
Tks.
Carlos
November 6, 2003 at 2:57 am
Hi,
Seems to work if you add a from clause...
if
(select count(*)
FROM
(SELECT getDate() as dtmToday where datepart(weekday,getdate()) not in (1,7)) t1 ) > 0
BEGIN
PRINT 'WEEKDAY'
END
ELSE
BEGIN
PRINT 'WEEKEND'
END
Thanks
Andy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply