I have the following query which uses a date variable, which is generated inside the stored procedure:
DECLARE @sp_Date DATETIME
SET @sp_Date = DateAdd(m, -6, GETDATE())
SELECT DISTINCT pat.PublicationID
FROM PubAdvTransData AS pat
INNER JOIN PubAdvertiser AS pa ON pat.AdvTransID = pa.AdvTransID
WHERE (pat.LastAdDate > @sp_Date) AND (pa.AdvertiserID = 12345))
The problem is that the @sp_Date value appears to be being ignored and I am wondering why? Have I defined or used it incorrectly?
This question is related to
sql
sql-server
date
sql-server-2008
If you manually write out the query with static date values (e.g. '2009-10-29 13:13:07.440') do you get any rows?
So, you are saying that the following two queries produce correct results:
SELECT DISTINCT pat.PublicationID
FROM PubAdvTransData AS pat
INNER JOIN PubAdvertiser AS pa
ON pat.AdvTransID = pa.AdvTransID
WHERE (pat.LastAdDate > '2009-10-29 13:13:07.440') AND (pa.AdvertiserID = 12345))
DECLARE @sp_Date DATETIME
SET @sp_Date = '2009-10-29 13:13:07.440'
SELECT DISTINCT pat.PublicationID
FROM PubAdvTransData AS pat
INNER JOIN PubAdvertiser AS pa
ON pat.AdvTransID = pa.AdvTransID
WHERE (pat.LastAdDate > @sp_Date) AND (pa.AdvertiserID = 12345))
Source: Stackoverflow.com