Select Date between Two Dates in SQL and Specific Dates Format

This code snippet is for Select Date between Two Dates in SQL and Specific Dates Format.

SQL server supports implicit conversion between character types (char, nchar, varchar, nvarchar) and date/time types (datetime, date, time, etc). Here I am create a temp table to show select dates using sql query.

CREATE TABLE #FindDateBW2Dates (
Date Datetime
)

INSERT INTO #FindDateBW2Dates(Date) values ('2015-04-28 00:00:00.000')
INSERT INTO #FindDateBW2Dates(Date) values ('2015-05-05 00:00:00.000')
INSERT INTO #FindDateBW2Dates(Date) values ('2015-05-05 00:00:00.000')
INSERT INTO #FindDateBW2Dates(Date) values ('2015-08-22 00:00:00.000')
INSERT INTO #FindDateBW2Dates(Date) values ('2016-06-02 00:00:00.000')
INSERT INTO #FindDateBW2Dates(Date) values ('2015-04-28 00:00:00.000')
INSERT INTO #FindDateBW2Dates(Date) values ('2015-01-02 00:00:00.000')
INSERT INTO #FindDateBW2Dates(Date) values ('2015-05-02 00:00:00.000')

select *
from #FindDateBW2Dates
where CAST( CONVERT(varchar(10), #FindDateBW2Dates.Date, 120) AS DATE) BETWEEN CAST('01/01/2016 12:00:00' AS DATE) AND CAST('06/04/2016 12:00:00' AS DATE)