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.

You're trying to *concatenate strings*, not use `STUFF` with `DATE`. SQL Server 2017 introduced STRING_AGG so there's no need to use string concatenation techniques like this. — Panagiotis Kanavos, Jan 29 '19 at 11:43
@Panagiotis As it currently stands I cannot use SQL Server 2017. — JimmyPop13, Jan 29 '19 at 11:45
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 — Panagiotis Kanavos, Jan 29 '19 at 11:46
"Also it has to return a DATE value" -- what does "it" refer to? You're concatenating a bunch of dates into a string. One way or another, that's not going to be a single `DATE` value. Adding some more input and desired output might help. — Jeroen Mostert, Jan 29 '19 at 11:48
All `STUFF` does is remove the leading comma and space from the final result. Far too many people ascribe too much power to `STUFF` which tends to mean that they're *using* the `STUFF`/`FOR XML` technique to concatenate strings without ever asking themselves *how that works*. — Damien_The_Unbeliever, Jan 29 '19 at 11:50
@Damien_The_Unbeliever you'll have to admit it takes even SQL Server MVPs a lot of time and a lot of answering SO questions to finally ... internalize ... how that works. Unless you're Itzik Ben-Gan or Aaron Bertrand — Panagiotis Kanavos, Jan 29 '19 at 12:06
Why are you converting to `.value('(./text())[1]','DATE')` when you actually want a substring `.value('(./text())[1]','VARCHAR(10)')`? — Salman A, Jan 29 '19 at 12:09

score 1 · Answer 1 · answered Jan 29 '19 at 11:45

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

I see. Thanks for putting it simply for me, it all makes sense now. I was so focused on the DATE part of the problem that I was ignoring the fact that I'm trying to use XML. — JimmyPop13, Jan 29 '19 at 11:48

Panagiotis Kanavos · Answer 2 · 2019-01-29T12:04:47.470

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.

Using STUFF with DATE

2 Answers2