subquery for more than one record

[tleper]

i have a sql2000 server,a table called MembersDNS and a column called ActivityDate.here is my code:

declare @ta char(20),@su char(20),@bityil char(20),@yil char(20)
set @ta=(select ActivityDate from MembersDNS where ID=957)
set @yil=substring(@ta,7,4)
set @su=(select DomainPeriod from MembersDNS where ID=957)
set @bityil=year(@yil)+@su

print datediff(dd,substring(@ta,4,2)+'.'+substring(@ta,1 ,2)+'.'+@bityil,getdate())

result is ok:9.but I have more than one record.when I rewrite the code without where clause I get the error:
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
" so how can I do this for all records.

[skhanal]

If you have to do row by row processing then you have to use a CURSOR and a WHILE loop.

But looking at the query it looks possible to use one SELECT statement to get date differences.

Post your requirement and some examples. I got confused with all the substrings and conversions.

[tleper]

what Im trying to do is get the date from table,take the difference between this date and today's date(with the getdate() method).if difference < 30 then make the dns inactive.the code I wrote this complicated cause the date column in table is char.but it works fine,for 1 record.how can I use while in sql query?a simple ex would be great.

[Rawhide]

I'm having to guess what each field is because I don't know your table schema. I'm assuming that DomainPeriod is the number of days for which the domain should be active and that ActivityDate is the date on which the DomainPeriod began. I made up a name for the field that indicates if a domain is active or not.

If I'm correct in my assumptions, the following query will set all domains to inactive if they have exceeded their domain period.

Update MemberDNS
Set DNSActive = 0
Where DateDiff(dd, getdate(), Cast(ActivityDate as DateTime)) < Cast(DomainPeriod As Int)

[tleper]

here is my MembersDNS table schema:
DomainName char(20)
DomainPeriod int -> (this column is number of years instead of days)
Activity smallint-> 0 or 1
ActivityDate char(20) -> dd/mm/yyyy
cast(ActivityDate) raises error in my server so I used substring to give the correct date form.
I have to take the ActivityDate,add DomainPeriod ,take difference between today and new date which is expiration date so if the difference < 0 than set the domain inactive.the code which I sent first was ok for one record.I think I need a while loop or something.thanks.

[tleper]

thanks I got the solution:
update MembersDNS set Activity=0
where Activity=2 and
datediff(dd,substring(ActivityDate,4,2)+'.'+substr ing(ActivityDate,1,2)+'.'+cast(cast(substring(Acti vityDate,7,4) as Int)+DomainPeriod as char),getdate())<0

and isdate(substring(ActivityDate,4,2)+'.'+substring(A ctivityDate,1,2)+'.'+substring(ActivityDate,7,4))= 1

[Rawhide]

Don't use substring, use cast. There will be a huge performance gain my way.

I'm going to guess that it's giving you the error when you try to cast it as a datetime because the value inside it is in the format of dd/mm/yyyy and the SQL Server is expected it to be in the format of mm/dd/yyyy. If you run the query "Select getdate()", which format is it using?

If I'm right, you can simply use the "Set DateFormat" command to tell it what format the dates are in.

This will cause an error on a SQL Server that is set to the U.S. English default of month/day/year:

Declare @TestDate char(20)
Set @TestDate = '24/10/2004'
Select Cast(@TestDate as datetime)

If I add the Set DateFormat command, it works:

Declare @TestDate char(20)
Set @TestDate = '24/10/2004'

Set DateFormat 'dmy'

Select Cast(@TestDate as datetime)


It could be that this is the reason you're using a char field instead of a datetime field for the dates.

[tleper]

thanks that works better..