SQL How to correctly set a date variable value and use it?
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))
Your syntax is fine, it will return rows where LastAdDate
lies within the last 6 months;
select cast('01-jan-1970' as datetime) as LastAdDate into #PubAdvTransData
union select GETDATE()
union select NULL
union select '01-feb-2010'
DECLARE @sp_Date DATETIME = DateAdd(m, -6, GETDATE())
SELECT * FROM #PubAdvTransData pat
WHERE (pat.LastAdDate > @sp_Date)
>2010-02-01 00:00:00.000
>2010-04-29 21:12:29.920
Are you sure LastAdDate
is of type DATETIME
?