How to add between date filter on PIVOT SQL query

Question

I have stored data with types and date recorded. I would like to get the total of each type per date. I have a [Transactions] table with the following pattern:

Id |   Type   |      Date_and_Time      |
----------------------------------------
1  |   Bags   | 2019-01-01 17:39:34.620 |
2  |   Shoes  | 2019-01-02 17:39:34.620 |
3  |   Shoes  | 2019-01-02 17:39:34.620 |
4  |   Bags   | 2019-01-02 17:39:34.620 | 
5  |  Shirts  | 2019-01-02 17:39:34.620 |
6  |  Shirts  | 2019-01-03 17:39:34.620 |
7  |  Shirts  | 2019-01-03 17:39:34.620 |  
...

I have working PIVOT query but without date filter:

DECLARE @cols NVARCHAR(max) = Stuff((SELECT DISTINCT ', ' + Quotename(Type) 
             FROM   Transactions
             FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, NULL);

EXECUTE('SELECT * FROM (select CAST(Date_and_Time as DATE) AS Transaction_Date, Type, Count(*) n 
                   from  #Tempsa GROUP BY CAST(Date_and_Time as DATE), Type) s
                    PIVOT (max(n) FOR Type IN (' +@cols + ')) pvt')

Output :
Transaction_Date |  Bags | Shirts | Shoes
------------------------------------------
|   2019-01-01   |  1    |  NULL  | NULL |
|   2019-01-02   |  1    |  1     | 2    |
|   2019-01-03   |  NULL |  2     | NULL |
....  

However, when I use the below query with between date filter I don't get any record:

DECLARE @STARTDATE nvarchar(100) = '01/01/2019'
DECLARE @ENDDATE nvarchar(100) = '01/03/2019'

DECLARE @cols NVARCHAR(max) = Stuff((SELECT DISTINCT ', ' + Quotename(Type) 
             FROM   #Tempsa
             FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, NULL);

EXECUTE('SELECT * FROM (select CAST(Date_and_Time as DATE) AS Transaction_Date, Type, Count(*) n 
                   from  #Tempsa WHERE Date_and_Time BETWEEN ' +@STARTDATE+ ' AND ' +@ENDDATE+' GROUP BY CAST(Date_and_Time as DATE), Type) s
                    PIVOT (max(n) FOR Type IN (' +@cols + ')) pvt')


Output :
Transaction_Date |  Bags | Shirts | Shoes
------------------------------------------
|                |       |        |       |
....

Answer 1

Just add quotes around the date:

DECLARE @STARTDATE nvarchar(100) = '01/01/2019'
DECLARE @ENDDATE nvarchar(100) = '01/03/2019'

DECLARE @cols NVARCHAR(max) = Stuff((SELECT DISTINCT ', ' + Quotename(Type) 
             FROM   #Tempsa
             FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, NULL);

EXECUTE('SELECT * FROM (select CAST(Date_and_Time as DATE) AS Transaction_Date, Type, Count(*) n 
                   from  #Tempsa WHERE Date_and_Time BETWEEN ''' +@STARTDATE+ ''' AND ''' +@ENDDATE+''' GROUP BY CAST(Date_and_Time as DATE), Type) s
                    PIVOT (max(n) FOR Type IN (' +@cols + ')) pvt')

Without double quotes, you are building something like this:

BETWEEN 01/01/2011 AND 01/03/2019

which is just a calculation that is evaluated by the engine

 SELECT 01/01/2011 -- 0
       ,01/03/2019 -- 0

and it is 0. So, you are asking to get all dates which are from 0 to 0. And that's why adding the quotes make your filtering criteria valid (it was valid before, but SELECT CAST(0 AS DATETIME) is 1900-01-01 00:00:00.000 and as your boundaries are the same, no records are returned).

Of course, you can use CONVERT(VARCHAR(10), @STARTDATE, 121) to ensure there are no misunderstandings during the implicit conversion.

Answer 2

I have modified the query shared by you to get the desired result. it was not working because single quite was missing while concatenating the date for the filter. Now it is working. You can verify the same.

DECLARE @STARTDATE nvarchar(100) = '01/01/2019'
DECLARE @ENDDATE nvarchar(100) = '01/03/2019'

DECLARE @cols NVARCHAR(max) = Stuff((SELECT DISTINCT ', ' + Quotename(Type) 
             FROM   #Tempsa
             FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, NULL);

EXECUTE( 'SELECT * FROM (select CAST(Date_and_Time as DATE) AS Transaction_Date, Type, Count(*) n 
                   from  #Tempsa WHERE Date_and_Time BETWEEN ''' + @STARTDATE+ ''' AND ''' +@ENDDATE+''' GROUP BY CAST(Date_and_Time as DATE), Type) s
                    PIVOT (max(n) FOR Type IN (' +@cols + ')) pvt')