Use of Date format in where clause in MS SQL Server

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.