How to add between date filter on PIVOT SQL query

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.