CODEWITHSUNDEEP
sql-serversql-like
Denoteone
Denoteone

Reputation: 4055

Using LIKE in ms sql server management studio

Question: When I use the LIKE in my query it is gray and my query does not return anything when I know if should.

I am not sure if there is a configuration setting I am missing but normally when I use MS sql server management studio terms like: UPDATE WHERE SELECT ADD all show as blue in my query window.

Example query: 

SELECT *
  FROM [MainSiteDB].[dbo].[usr_user]
  WHERE [MainSiteDB].[dbo].[usr_user].[usr_lastLogin] LIKE '2014-11-10%'

In my table in that field there are many entries like: 2014-11-10 13:19:46.967

Upvotes: 0

Views: 5852

Answers (6)

Veera
Veera

Reputation: 3492

Try this query. Using LIKE directly in DateTime filed is won't give expected result. Convert the DateTime field to specific format to Varchar and use LIKE.

101 - Format the DateTime to mm/dd/yyy

SELECT *
  FROM [MainSiteDB].[dbo].[usr_user]
  WHERE CONVERT(VARCHAR(20), [MainSiteDB].[dbo].[usr_user].[usr_lastLogin], 101) 
  LIKE '05/22/2014%'

105 - Format the DateTime to mm-dd-yyy

SELECT *
  FROM [MainSiteDB].[dbo].[usr_user]
  WHERE CONVERT(VARCHAR(20), [MainSiteDB].[dbo].[usr_user].[usr_lastLogin], 105) 
  LIKE '05-22-2014%'

Upvotes: 0

Nima MashhadiZadeh
Nima MashhadiZadeh

Reputation: 167

you Can Use it :

SELECT * FROM [MainSiteDB].[dbo].[usr_user] WHERE DATEADD(dd, 0, DATEDIFF(dd, 0, [MainSiteDB].[dbo].[usr_user].[usr_lastLogin]))= '2014-05-9'

Upvotes: 1

amnippon
amnippon

Reputation: 321

You can find in menu in Management studio menu under Tools->Option then Fonts and color. Then you can change the customize color for SQL keywords.

Upvotes: 1

knkarthick24
knkarthick24

Reputation: 3216

select * from tablename
where CONVERT(varchar(10), getdate(), 105) = @inputdate

Below convert will fetch the datepart alone from your columnname, and then based on your input it will fetch all matching records.

SELECT CONVERT(varchar(10), getdate(), 105) -->  11-11-2014

Upvotes: 0

Paresh J
Paresh J

Reputation: 2419

You can cast the date part and then use the LIKE operator. Check the below script:

Select * from TableName Where colName like Cast('2014-09-25' as datetime)

Upvotes: 0

amnippon
amnippon

Reputation: 321

Like is not applicable for datetime or date field for SQL server. 'Like' is for varchar/char/text and other text related field

You can use between or <> sign 

Select * from tblTable where Date between '2014 Jan 01' and '2014 Jan 31'

OR

Select * from tblTable where Date >= '2014 Jan 01' and Date <= '2014 Jan 31'

Upvotes: 3

Related Questions