Solved

MySQL query with substring

Posted on 2014-01-07
3
328 Views
Last Modified: 2014-01-07
What is wrong with this:

SELECT * FROM `citrans` WHERE substring(dtstamp,0,4) = '2012'.

See attached for structure of table citrans.

There are hundreds of records with year = 2012.
citrans-str.jpg
0
Comment
Question by:Richard Korts
3 Comments
 
LVL 83

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 250 total points
ID: 39763803
Are you getting an error message?  MySQL doesn't keep the DATETIME columns in text format but in an internal binary format.  It converts them to text when it returns them.

I wouldn't do it that way.  This is what I use.
SELECT * FROM `citrans` WHERE `dtstamp` >= '2012-01-01' AND `dtstamp` < '2013-01-01'

Open in new window

Without a time, MySQL makes it 0 so it is actually using '2013-01-01 00:00:00'.
0
 
LVL 11

Accepted Solution

by:
Murfur earned 250 total points
ID: 39763809
SUBSTRING is for text fields, better to use a date function:

SELECT * FROM citrans WHERE YEAR( dtstamp ) = '2012'
0
 
LVL 17

Expert Comment

by:Kent Dyer
ID: 39763850
Have a look here - I think this what you are looking for..

http://sqlfiddle.com/#!2/c00121/4
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

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Creating and Managing Databases with phpMyAdmin in cPanel.

679 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