How do you write a sql statement that creates a new column for a date value and then queries it in the WHERE clause

Question

I have written a sql statement as so:

SELECT * 
FROM (
   SELECT DATEADD(dd, 60, PaymentDate) AS NewPaymentDate, * 
   FROM MyTable
) x 
WHERE x.NewPaymentDate >=" & Date() & " 
  AND Archived=0

But it is returning

Operand type clash: date is incompatible with int

PaymentDate is a Date format in the database - but do i have to format the new date column 'NewPaymentDate' into a date format and if so, how do i do this?

Thanks

Answer 1

This looks like SQL Server syntax. Why do you need to pass in the current date? Just use the database date:

SELECT t.* 
FROM (SELECT DATEADD(day, 60, PaymentDate) AS NewPaymentDate, t.* 
      FROM MyTable t
     ) t
WHERE t.NewPaymentDate >= CONVERT(DATE, GETDATE()) AND
      t.Archived = 0;

If you want to pass a value in, learn to use parameters to pass values in -- the syntax varies depending on the API you are using for accessing the database.