• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 245
  • Last Modified:

Convert date to text

A bit of a strange one, but I want to search a date field as a text

eg:-
*2002* which would look for 2002/1/1 - 2002/12/31

I have searched for a way to convert to text, such as text('date'). I don't want to convert the field to a text, as I still want to order by the field.
0
tonelm54
Asked:
tonelm54
2 Solutions
 
sognoctCommented:
select *
from tblTable 
where DATE_FORMAT(yourDate, '%Y /%m/%d') like '%2012%' 
order by yourDate

Open in new window


but consider also the idea to use datetime in query

SELECT * 
FROM  tblTable 
WHERE YEAR( mydate ) =2013
order by yourDate

Open in new window

0
 
johanntagleCommented:
If possible depending on the types of expected search strings I would recommend converting the search string to a date range.  For example  in your application code if it detects that the search string as a year then in your SQL do a:

WHERE date_column between '2012-01-01' and '2012-12-31'

(or '2012-12-31 23:59:59', if date_column is actually a datetime with time defined)

The problem with doing date_format or year or any other function on the date_column is that it will always result in a full table scan, even if you have an index defined for date_column.  Big no-no if the table is of considerable size.

I wouldn't advise doing a LIKE '%2012%' on a string column either, whether you convert the existing or create another column -- that's another performance killing full table scan.  At least take a look at MySQL FULL-TEXT search (see dev.mysql.com/doc/refman/5.1/en/fulltext-search.html and devzone.zend.com/26/using-mysql-full-text-searching/)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now