Merlin Merlin - 1 month ago 18
SQL Question

Handling Date Comparison in WHERE statement

I am attempting to pull back records that have a birthday in a particular month, of a certain membership type, who are active AND have checked into the facility within the last 30 days. But I am having difficulty with the 30 logic. Every way that I attempt to calculate the date the query in MS SQL errors out.

I keep getting a syntax error related to the date calcualtion.

SELECT
MEMBERS.scancode, MEMBERS.lname, MEMBERS.fname, MEMBERS.dob,
MEMBERS.status, MEMBERTYPES.description, MEMBERS.mtypeid,
SITES.sitename, MEMBERS.email
FROM MEMBERS
INNER JOIN SITES ON MEMBERS.siteid = SITES.siteid
INNER JOIN MEMBERTYPES ON MEMBERS.mtypeid = MEMBERTYPES.mtypeid
WHERE (MEMBERS.siteid = @rvSite)
AND (MEMBERS.status = 'A')
AND (MONTH(MEMBERS.dob) = @rvMonth)
AND (MEMBERS.relationship = 'M')
AND (MEMBERS.mtypeid IN (1, 10, 12, 22, 28, 32, 33, 34, 35, 36, 40, 48))
AND (DATEDIFF(MEMBERS.lastvisit, GETDATE()) <= 30)

vkp vkp
Answer

DATEDIFF function requires 3 arguments datepart , startdate , enddate. You were missing the first argument (datepart) in the query. Change it to

DATEDIFF(DD,MEMBERS.lastvisit, GETDATE()) <= 30
Comments