Example of using Date format in Where or Select clause in MSSQL Server

Example to show cast varchar to date and use varchar date in where clause of MS SQL SERVER

--Check date format:
select getdate()

2022-04-05 19:58:38.337

-- Use CAST function to convert varchar datatype to Datetime:
select cast('2022-04-15' as datetime)

2022-04-15 00:00:00.000

--Example of Using Convert to show different format which help in where or Select Query:
SELECT CONVERT(VARCHAR, cast('2022-04-15' as datetime), 105)

15-04-2022
SELECT CONVERT(VARCHAR, cast('2022-04-15' as datetime), 101)
04/15/2022
SELECT CONVERT(VARCHAR, cast('2022-04-15' as datetime), 103)
15/04/2022
SELECT CONVERT(VARCHAR, cast('2022-04-15' as datetime), 104)
15.04.2022


-- Example of using and showing in select as 'MM/DD/YYYY'
select CONVERT(VARCHAR,ModifiedDate,105) from [Person].[CountryRegion] where ModifiedDate = '04/30/2008'

30-04-2008

--Example of using 'MM-DD-YYYY' in where clause 
select * from [Person].[CountryRegion] where ModifiedDate = '04-30-2008'

Output:
AD	Andorra	2008-04-30 00:00:00.000

-- Example of using 'DD-MM-YYYY' in where clause
select * from [Person].[CountryRegion] where  CONVERT(VARCHAR, ModifiedDate, 105) = '30-04-2008'

Output:
AD	Andorra	2008-04-30 00:00:00.000