Using STUFF with DATE

Question

I am trying to use the STUFF functionality in SQL Server 2016 to select DATE information and return it to a table. Occasionally there are multiple dates to return. I have already used STUFF to get other data I need.

Email = STUFF((SELECT ', ' + [Value] 
  FROM EmailTable  
    WHERE ID = r.ID AND EmailType = 1 
      FOR XML PATH(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)'),1,2,'')

This can return multiple emails where applicable and works fine. Now the problem is I want to do the same but with a DATE.

Date = STUFF((SELECT ', ' + DateValue 
  FROM DateTable  
    WHERE ID = r.ID
      FOR XML PATH(''),TYPE).value('(./text())[1]','DATE'),1,2,'')  

The above code snippets are just examples, the table and variable names are different in reality, but they should convey what I'm getting at.

The error for the last code snippet is that it will not work because of the '+' symbol. If I take the comma and plus out I can get it to return DATEs but they are wrapped in XML tags.

Also it has to return a DATE value, it can not be converted to NVARCHAR.

I'm not sure if what I am after is doable but I thought I would ask.

If any more information is needed please ask.

Answer 1

This is too long for a comment.

Your code cannot return multiple date values in a single column. SQL Server does not offer array types -- or their equivalent.

What FOR XML PATH does is produce a string. The string is formatted as XML, but it is still a string. Within a string, you can identify the types of attributes. However, the representation is as a string.

The best you can do is to store the date as YYYY-MM-DD or YYYYMMDD, which is readily convertible to a date in SQL Server (and in almost any other software).

Answer 2

What you posted ins a string aggregation technique used in older SQL Server versions. SQL Server 2017 provides STRING_AGG for this.

This technique generates an XML value from a query using the empty string as an element name. .value('(./text())[1]','NVARCHAR(MAX)') at the end converts the XML value to text . Finally, STUFF, 1,2,'') removes the leading delimiter.

Since you want to concatenate strings, and don't want to leave date formatting to chance, use FORMAT() to format the date into the string you want:

Email = STUFF((SELECT ', ' + FORMAT([DateValue],'yyyy-MM-dd')
                   FROM EmailTable  
                   WHERE ID = r.ID AND EmailType = 1 
                   FOR XML PATH(''),TYPE
               ).value('(./text())[1]','NVARCHAR(MAX)'),
              1,2,'')

How it works

Executing the innermost query with x as the element name :

SELECT ', ' + format(DateValue,'yyyy-MM-dd')
FROM EmailTable
WHERE year=2019 and Day<5 and month=1
FOR XML PATH('x'),TYPE

Would return :

<x>, 2019-01-01</x>
<x>, 2019-01-02</x>
<x>, 2019-01-03</x>
<x>, 2019-01-04</x>

By specifying the empty string as the element, we get :

, 2019-01-01, 2019-01-02, 2019-01-03, 2019-01-04

That's still an XML value whose inner text is the string we want. We need to extract that with .value('(./text())[1]','nvarchar(max)') :

select (  SELECT ', ' + format(DateValue,'yyyy-MM-dd')
          FROM EmailTable
          WHERE year=2019 and Day<5 and month=1
         FOR XML PATH(''),TYPE
       ).value('(./text())[1]','nvarchar(max)')

After that, we need to remove the leading delimiter, in this case the two-char . T-SQL doesn't have aREMOVEstring function andSUBSTRINGneeds a length.STUFF` deletes a specified number of characters before appending a new string, so it can be used to remove text from the start.