Expiry Date Question

[WJProctor]

Hi there, i want to select from a database items that are out of date, by year and month. So i want to create a statement that looks for items that year value >= this year and then if this is true, it looks for items in those results that month value > this month. At the moment if we take todays date 02/2004 then the query ive written would pick out 02/2005 as well which isnt right. Hope this makes sense.

Regards

James Proctor

http://www.ebmis.co.uk

 

[nigelrivett]

How are your dates stored?
First get the begining of the month then compare with that

declare @d datetime
select @d = convert(varchar(6),getdate(),112) + '01'

select ...
from tbl
where dte < @d

You could do it all in a single query if you wish
select ...
from tbl
where dte < convert(varchar(6),getdate(),112) + '01'


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[WJProctor]

Thanks for that, not 100% sure thats what i mean. I should have written it more like this.

If Year(ExpiryDate) >= TodayYear Then
If Month(ExpiryDate) > TodayMonth Then
Return Row
End If
End If

If there a way that this can be written into a select statement. At the moment i have:

SELECT * FROM Table WHERE Year(ExpiryDate) >= TodayYear AND Month(ExpiryDate) > TodayMonth

I can see what it is doing. But cant understand how to change it. Hope you can help me out.

Regards

James Proctor

http://www.ebmis.co.uk