MySQL Server
MySQL Server

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 KortsBusiness Owner / Chief DeveloperAsked: 2014-01-07

Who is Participating?

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license! Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 85

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'

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

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