SQL如何正确设置和使用日期变量值？ [英] SQL How to correctly set a date variable value and use it?

问题描述

我有以下使用日期变量的查询，该日期变量在存储过程内部生成：

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))

问题在于@sp_Date值似乎被忽略了，我想知道为什么吗？我定义或使用不正确吗？

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?

推荐答案

您的语法很好，它将返回 LastAdDate所在的行位于最近6个月内；

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

您确定 LastAdDate 的类型是 DATETIME 吗？

这篇关于SQL如何正确设置和使用日期变量值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！