How to compare a date with system date?

Question

Using SQL Server 2000 and VB6

Table1

ID  Date 

001 20090801 
001 20090802
…

001 20090831

002 20090801 
002 20090802
…

002 20090831

So on…,

I want to compare a date with system date, suppose today date is 20090831, I want to display a message as “today is last day for 001”

How to make a code in vb6 or is possible to make a query for this condition?

Need Help

Answer 1

You can do it all in a query:

SELECT 'Today is the last day for ' + ID 
FROM (SELECT ID, MAX(Date) as Date FROM Table1) t1
WHERE DATEPART(dayofyear, Date) = DATEPART(dayofyear, GETDATE()) 
AND DATEPART(year, Date) = DATEPART(year, GETDATE())

However, this assumes that you're using DATETIME datatypes and not strings for Date. If you're using strings, it'd be best to generate the system date inside your app, pass it to the query as a stored procedure parameter and use 'WHERE Date = @systemdate'

edit: fixed formatting