Hi. I am having this query
Code:
DECLARE @DateFrom as DateTime,
@DateTo as DateTime



SET @DateFrom = CONVERT(DATETIME,CONVERT(VARCHAR,DATEADD(hh,-6,getdate()),101))--CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE()-1,101))
SET @DateTo = CONVERT(DATETIME,CONVERT(VARCHAR,DATEADD(hh,-6,getdate()+1),101))--CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE(),101))




		declare @datetimefrommv as nvarchar(30)
		declare @datetimetomv as nvarchar(30)


SET		@DateFrom		= CONVERT(DATETIME,CONVERT(VARCHAR,DATEADD(hh,-6,getdate()),101))--CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE()-1,101))
SET		@DateTo			= CONVERT(DATETIME,CONVERT(VARCHAR,DATEADD(hh,-6,getdate()+1),101))--CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE(),101))




 set @datetimefrommv = CONVERT(VARCHAR(30),DATEADD(hh,-6,getdate()),112)
 set @datetimetomv = CONVERT(nvarchar(30), DATEADD(hh,-6,getdate())+1, 112)
  set @datetimefrommv = @datetimefrommv + ' 00:00:00.000'
  set @datetimetomv = @datetimetomv + ' 00:00:00.000'
 select @datetimefrommv 
 select @datetimetomv
 --select @datetimetomv



		
	SELECT	@DateFrom, @DateTo,
			C.Cinema_strCode, 
			C.Cinema_strName,
			CASE S.CinOperator_strCode 
			WHEN 'COSO' THEN 1
			WHEN 'RENO' THEN 1
			ELSE 0 END AS IsCool,
			F.HOPK as Film_strCode,
			F.Film_strTitleAlt,
			COUNT(*) AS Admissions
	FROM tblTrans_Ticket T
	Left Outer JOIN tblSession S 
		ON S.Session_lngSessionID = T.Session_lngSessionID
	LEFT OUTER JOIN tblCinema_Operator CO
		ON CO.CinOperator_strCode = S.CinOperator_strCode
	LEFT OUTER JOIN tblCinema C
		ON CO.Cinema_strCode = C.Cinema_strCode
	LEFT OUTER JOIN tblFilm F
		ON F.Film_strCode = S.Film_strCode
		
	WHERE	T.TransT_strType='P' 
			AND T.TransT_strStatus='V'
		AND S.Session_dtmRealShow BETWEEN @DateFrom AND @DateTo
			AND S.Session_dtmRealShow BETWEEN '20141027 00:00:00.000' AND '20141028 00:00:00.000'
		--	AND S.Session_dtmRealShow BETWEEN LEFT(CONVERT(VARCHAR, @DateFrom, 120), 20) AND LEFT(CONVERT(VARCHAR, @Dateto, 120), 20)
			--	AND S.Session_dtmRealShow BETWEEN @datetimefrommv AND @datetimetomv
			
			
	GROUP BY C.Cinema_strCode, C.Cinema_strName,
	         CASE S.CinOperator_strCode 
			 WHEN 'CS' THEN 1
			 WHEN 'RN' THEN 1
			 ELSE 0 END,
			 F.HOPK, F.Film_strTitleAlt
So what i do is 3 things. I am using a datetime in:
AND S.Session_dtmRealShow BETWEEN @DateFrom AND @DateTo
nvarchar variable in:
AND S.Session_dtmRealShow BETWEEN @datetimefrommv AND @datetimetomv
and finally manually nvarchar in:
AND S.Session_dtmRealShow BETWEEN '20141027 00:00:00.000' AND '20141028 00:00:00.000'
The last one runs in one second. The others in 2 minutes.
I am trying to simulate the manual '20141028 00:00:00.000' with nvarchar variables:
@datetimefrommv AND @datetimetomv but it will do 2 minutes run.
What is the deal here and any advice?
Thanks.