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 KortsAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
MurfurConnect With a Mentor Full Stack DeveloperCommented:
SUBSTRING is for text fields, better to use a date function:

SELECT * FROM citrans WHERE YEAR( dtstamp ) = '2012'
0
 
Dave BaldwinConnect With a Mentor Fixer of ProblemsCommented:
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
 
Kent DyerIT Security Analyst SeniorCommented:
Have a look here - I think this what you are looking for..

http://sqlfiddle.com/#!2/c00121/4
0
All Courses

From novice to tech pro — start learning today.