?
Solved

Convert date to text

Posted on 2013-01-06
2
Medium Priority
?
243 Views
Last Modified: 2013-01-28
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
Comment
Question by:tonelm54
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 9

Assisted Solution

by:sognoct
sognoct earned 1000 total points
ID: 38749164
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
 
LVL 24

Accepted Solution

by:
johanntagle earned 1000 total points
ID: 38749428
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

How To Reduce Deployment Times With Pre-Baked AMIs

Even if we can't include all the files in the base image, we can sometimes include some of the larger files that we would otherwise have to download, and we can also sometimes remove the most time-consuming steps. This can help a lot with reducing deployment times.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question