Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Convert date to text

Posted on 2013-01-06
2
Medium Priority
?
244 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
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

610 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