Click here to Skip to main content
15,867,453 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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

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
 
Share this answer
 
v2
SQL
select CustomerID from Orders 
where convert(nvarchar(50), OrderDate,126) LIKE '1996-07- %'
 
Share this answer
 
v2
Comments
kesav prakash 28-Jun-13 5:47am    
may i know what is that 126 inside the convert()
Try this,,,,,:)


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



Regards,
Nirav Prabtani.
 
Share this answer
 
Comments
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
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.
 
Share this answer
 
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!
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900