Status: Solved
Priority: Medium
Security: Public
Views: 379
Last Modified: 2014-01-07

MySQL query with substring

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

Richard Korts

Asked: 2014-01-07

Advertise Here

MySQL Server

2 Solutions

LVL 84

Dave BaldwinFixer of ProblemsCommented: 2014-01-07

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'.

LVL 11

MurfurFull Stack DeveloperCommented: 2014-01-07

SUBSTRING is for text fields, better to use a date function:

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

LVL 17

Kent DyerIT Security Analyst SeniorCommented: 2014-01-07

Have a look here - I think this what you are looking for..

http://sqlfiddle.com/#!2/c00121/4

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

MySQL query with substring

Join & Write a Comment Already a member? Login.

Featured Post