带日期的动态sql语法

Question

我的动态sql中的这一行提供了一些动态透视，似乎没有接受正确的日期并返回预期的结果。查询运行，不返回任何结果。

and PA.DATE_RECEIVED BETWEEN 
'+ convert(varchar(10), @Startdate, 120) +' AND '+ convert(varchar(10), @Enddate, 120) +'

检查它正在拾取的值...存储过程中的一些sql。

select convert(varchar(10), @Startdate, 120) - 2013-02-02    
select convert(varchar(10), @Enddate, 120) - 2013-02-26

在普通sql中运行外部查询，它能正常工作吗？问题出在哪里？

select COALESCE( PT.[description] , 'Grand Total') AS [Transaction Type], 
           Sum (AI.PRICE_INC_VAT) AS [AMOUNT (ú) CREDIT],
           P.[DESCRIPTION] AS [PRODUCT TYPE]    
From  [dbo].[T1] C     
join [dbo].[T2] S on S.[Customer_ID]=C.[Customer_ID]     
join [dbo].[T3] SO on SO.[SITE_ID]=S.[SITE_ID]    
join [dbo].[T4] OI on OI.[ORDER_ID]=SO.[SITE_ORDER_ID]    
left join [dbo].[T5] P on P.[PRODUCT_ID]=OI.[PRODUCT_ID]    
JOIN [dbo].[T6] AI ON  AI.ORDER_ITEM_ID = OI.ORDER_ITEM_ID    
JOIN T7 JBAI ON JBAI.ACTION_ITEM_ID = AI.ACTION_ITEM_ID    
JOIN T8 JB ON JB.JOB_BATCH_ID = JBAI.JOB_BATCH_ID    
JOIN T9 PA on PA.PAYMENT_ID=JB.PAYMENT_ID    
LEFT JOIN T10 CU ON JB.CUSTOMER_USER_ID = CU.CUSTOMER_USER_ID    
JOIN T11 PT ON PT.PAYMENT_TYPE_ID=PA.PAYMENT_TYPE_ID     
LEFT JOIN T12 SU ON SU.SYS_USER_ID=JB.SYS_USER_ID   
where P.[PRODUCT_CATEGORY_ID]= ( 
                        select PC.[PRODUCT_CATEGORY_ID] from [dbo].[PRODUCT_CATEGORY] PC 
                        where PC.[DESCRIPTION]='BAGS')     
and C.COMPANY_ID= '12'    
and PA.DATE_RECEIVED BETWEEN '02-FEB-2013' AND '26-FEB-2013'    
group by PT.DESCRIPTION, P.DESCRIPTION 

Answer 1

我觉得你的报价有问题。注意转义引号以使单引号出现在动态查询中

and PA.DATE_RECEIVED BETWEEN‘’+ convert(varchar(10)，@Startdate，120) +‘’AND‘’+ convert(varchar(10)，@Enddate，120) +‘

Answer 2

您可能遗漏了日期前后的单引号，而不是PA.DATE_RECEIVED BETWEEN 2013-02-02 AND 2013-02-26，请尝试将字符串改为：PA.DATE_RECEIVED BETWEEN '2013-02-02' AND '2013-02-26'。下面是一个如何在字符串中获取单引号的示例：

DECLARE @var VARCHAR(1000) = 
    'and PA.DATE_RECEIVED BETWEEN ''' + 
    convert(varchar(10), GETDATE(), 120) +
    ''' AND ''' + 
    convert(varchar(10), GETDATE(), 120) + ''''

SELECT @var

文字字符串中的单引号由''表示。

Answer 3

你有没有试过这个：

and PA.DATE_RECEIVED BETWEEN @Startdate AND @Enddate

您不必转换日期即可使BETWEEN语句工作