Using 'LIKE' with date field.

[cawthor]

Is there anyway to use the LIKE function on a date field? I have a web form where the user can input a date to query (or partial date). I was hoping the user could enter something like '%/3/2005' which would return all matching records. However, LIKE does not work on a text field. I have got it somewhat working by using the following convert on the field before I do the like comparison:

CONVERT (NVARCHAR, field_name, 101)

The only problem with this is that it changes the date to text in mm/dd/yyyy format. If the user were to do a search on '%/3/2005', then this would not return any matches. The user would need to enter '%/03/2005'.

Any ideas would be appreciated.

 

[Catadmin]

Try using LIKE '%/%3/2005' or LIKE '%3/2005' options.

Or you could use the MONTH, DAY, YEAR keywords (see Books Online) to do something similar to:

Select * from Tbl where datefield like Month(datefield) = 3

Alternately:

Select * from Tbl where datefield like MonthName(datefield) = 'March'

Does that help?



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[Pattycake245]

Or maybe it's a web side issue, where you do not give them the option for a partial date, rather a date range so that they can look at one or more months in a year if they want.

Tim

 

[Catadmin]

Tim has an excellent point.

But I just had another thought. Why does the date parameter on your webform have to be text? Can't you just stick a calendar control on that spot. Use it as a single date pick OR set up 2 calendar controls for a Start and END date like Tim suggested?



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[cawthor]

yeah I may have to restrict it from the web front end. I originally thought the suggestion by catadmin above would work ('Try using LIKE '%/%3/2005' or LIKE '%3/2005' options.'). However, if the user entered '%/3/2005' then the query would return %/03/2005, %/13/2005, and %/23/2005.

 

[vongrunt]

Suggestion: avoid LIKE stuff on dates converted to string like plague.

That way server must CONVERT() each value in table column against pattern, and query response time will get ugly (table scan). Besides, nvarchar is kind of waste - converted date has only lower-ASCII characters.

Try to solve this at user-interface level. For example, make two fields from-to as suggested. If user enters incomplete date, "autocomplete" values to the beginning (from) or end (to) of interval and then send exact dates to database/query.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)