15,670,257 members
See more: , +
How do you perform a LIKE statement on a column of DateTime datatype in SQL Server? I had a SQL Query Like this:
SQL
`select CustomerID from Orders where OrderDate LIKE '1996-07- %'`

this returns me nothing!
Posted
Updated 8-Sep-22 19:33pm
v2

## Solution 1

Instead you can use the DATEPART[^] function to extract portions of dates. Try like this:
SQL
```SELECT * FROM TableName where DATEPART(yy, YourDateColumn) = 1996 --For Year
SELECT * FROM TableName where DATEPART(mm, YourDateColumn) = 4--For Months
SELECT * FROM TableName where DATEPART(dd, YourDateColumn) = 4--For day

--Or, For all togather

SELECT * FROM TableName where (DATEPART(yy, YourDateColumn) = 1996 AND DATEPART(mm, YourDateColumn) = 4 AND DATEPART(dd, YourDateColumn) = 4)```

Like operator will not work with DateTime. Please see this[^].

--Amit

v2

## Solution 2

SQL
```select CustomerID from Orders
where convert(nvarchar(50), OrderDate,126) LIKE '1996-07- %'```

v2
kesav prakash 28-Jun-13 5:47am
may i know what is that 126 inside the convert()

## Solution 3

Try this,,,,,:)

SQL
`SELECT * FROM TableName WHERE  CONVERT(VARCHAR, DateTimeColumn , 120) LIKE '2013-06%'`

Regards,
Nirav Prabtani.

kesav prakash 28-Jun-13 5:35am
thank u frined i got the right answer
Nirav Prabtani 28-Jun-13 5:40am
Welcome, Kesav..:)
kesav prakash 28-Jun-13 5:47am
may i know what is that 120 inside the convert()
Nirav Prabtani 28-Jun-13 5:51am
Yes kesav it is Date time conversion in many formats like.....

SELECT convert(datetime, '10/23/2016', 101) -- mm/dd/yyyy
SELECT convert(datetime, '2016.10.23', 102) -- yyyy.mm.dd ANSI date with century
SELECT convert(datetime, '23/10/2016', 103) -- dd/mm/yyyy
SELECT convert(datetime, '23.10.2016', 104) -- dd.mm.yyyy
SELECT convert(datetime, '23-10-2016', 105) -- dd-mm-yyyy
-- mon types are nondeterministic conversions, dependent on language setting
SELECT convert(datetime, '23 OCT 2016', 106) -- dd mon yyyy
SELECT convert(datetime, 'Oct 23, 2016', 107) -- mon dd, yyyy
-- 2016-10-23 00:00:00.000
SELECT convert(datetime, '20:10:44', 108) -- hh:mm:ss
-- 1900-01-01 20:10:44.000

-- mon dd yyyy hh:mm:ss:mmmAM (or PM) - sql time format - SQL Server datetime format
SELECT convert(datetime, 'Oct 23 2016 11:02:44:013AM', 109)
-- 2016-10-23 11:02:44.013
SELECT convert(datetime, '10-23-2016', 110) -- mm-dd-yyyy
SELECT convert(datetime, '2016/10/23', 111) -- yyyy/mm/dd
-- YYYYMMDD ISO date format works at any language setting - international standard
SELECT convert(datetime, '20161023')
SELECT convert(datetime, '20161023', 112) -- ISO yyyymmdd
-- 2016-10-23 00:00:00.000
SELECT convert(datetime, '23 Oct 2016 11:02:07:577', 113) -- dd mon yyyy hh:mm:ss:mmm
-- 2016-10-23 11:02:07.577
SELECT convert(datetime, '20:10:25:300', 114) -- hh:mm:ss:mmm(24h)
-- 1900-01-01 20:10:25.300
SELECT convert(datetime, '2016-10-23 20:44:11', 120) -- yyyy-mm-dd hh:mm:ss(24h)
-- 2016-10-23 20:44:11.000
SELECT convert(datetime, '2016-10-23 20:44:11.500', 121) -- yyyy-mm-dd hh:mm:ss.mmm
-- 2016-10-23 20:44:11.500

-- Style 126 is ISO 8601 format: international standard - works with any language setting
SELECT convert(datetime, '2008-10-23T18:52:47.513', 126) -- yyyy-mm-ddThh:mm:ss(.mmm)
-- 2008-10-23 18:52:47.513
SELECT convert(datetime, N'23 شوال 1429 6:52:47:513PM', 130) -- Islamic/Hijri date
SELECT convert(datetime, '23/10/1429 6:52:47:513PM', 131)

& Many More.....:)

Regards,
Nirav Prabtani.
Member 11518135 25-Jan-16 4:56am
I want to display list of ename who joined in the month of second character is 'a'.
I tried this bu it wouldn't work
Tell me the exact one and im doing it on sql server 2008r2.
-Select ename from emp where datepart(month,hiredate)like '_a%'
Plz suggest me proper ans
Thanx
Aniket

## Solution 5

As I learned it, conversions are slow and best to be avoided where possible. A simple way to search would be with DateTime arithmetic:

SQL
```SELECT CustomerID FROM Orders
WHERE OrderDate > '1996-07-01' AND OrderDate < '1996-08-01'```

'YYYY-MM-DD' defaults to YYYY-MM-DD 00:00:00.00, so this covers the full month midnight to midnight.

## Solution 6

select * from Table_name where transaction_date between '2022-09-07 00:00:00.000' and '2022-09-07 23:59:59.000';

you're welcome!

v2