从Visual Studio报表的查询结果创建文本的子字符串

Question

我正试图从日期查询结果中创建一个子字符串。我希望在Visual Studio SSRS中创建的延迟付款通知中使用此项。我的查询基本查询是：

select Distinct a.inv_date
from PastDue_Master a
left join
PastDue_Mmbr_List b on a.ID_NUM = b.ID_NUM
where (DAYS_PAST_DUE >= 30 and [60d_Notice_Date] is null)
and Crctd_Inv_Flag = 0
and Facility_Name = 'Facility Name'

具有的结果集

2019-02-28 00:00:00.000  
2019-05-31 00:00:00.000  
2019-08-31 00:00:00.000  
2019-11-30 00:00:00.000  

我尝试过以下几种：

Declare @datestring varchar (8000)
select @datestring = stuff((select ',' +convert(nvarchar(20), SNAF_Inv_Date)
FROM PastDue_Master a
left join PastDue_Mmbr_List b on a.ID_NUM = b.ID_NUM
where (DAYS_PAST_DUE >= 30 and [60d_Notice_Date] is null)
and Crctd_Inv_Flag = 0
and Facility_Name = 'Facility Name' FOR XML PATH('')),1, 1, '')

但我得到了"；命令已成功完成"没有结果集，我正在寻找

02/28/2019, 05/31/2019, 08/31/2019, 11/30/2019

Answer 1

您正在为@datestring赋值，但在代码中没有使用@datestring。如果添加：SELECT @datestring或PRINT @datestring，则会看到返回的内容。

注意此代码：

DECLARE @demo TABLE (dt VARCHAR(30));
INSERT @demo VALUES
('2019-02-28 00:00:00.000'),  
('2019-05-31 00:00:00.000'),  
('2019-08-31 00:00:00.000'),  
('2019-11-30 00:00:00.000');
DECLARE @datestring VARCHAR(8000);
SELECT @datestring =  STUFF((
SELECT CONCAT(' ,',IIF(MONTH(f.Dt)<10,'0',''),MONTH(f.Dt) ,'/',DAY(f.Dt),'/',YEAR(f.Dt))
FROM   @demo AS d
CROSS APPLY (VALUES(CAST(d.Dt AS DATE))) AS f(Dt)
FOR XML PATH('')),1,2,'');
SELECT @datestring;
PRINT @datestring;

Answer 2

因为您要分配给一个单独使用的varchar变量，所以不需要任何昂贵的内联级联，只需执行以下操作即可：

declare @datestring varchar(max)='';
select @datestring+=',' + Convert(varchar(10),inv_date,101)
from a /* Insert your required query here */
set @datestring=Stuff(@datestring,1,1,'');
select @datestring;