Convert SYSTEM DATE to YYYY-MM-DD and use it inside single quotes inside a query in SQL Server

I am trying to get the system date and store it inside a variable and use that inside single quotes as a value inside a query

DECLARE @Tdate VARCHAR(20);

SET @Tdate = CONVERT(DATE, GETDATE());

SELECT COUNT(*) 
FROM Seymr 
WHERE BatchCreated BETWEEN '@Tdate 00:00:00.00' AND '@Tdate 05:00:00.00'

I get the following error – what is the correct syntax to use @Tdate inside single quotes?

Answer

You don’t need such syntax, you need to use the correct types – date, datetime2 etc, not strings.

Assuming BatchCreated uses a date-related type, you can use :

declare @from datetime2(0)=cast(getdate() as date)
declare @to datetime2(0)=dateadd(hour,5,@from)

select count(*) from Seymr 
where BatchCreated between @from and @to