• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 379
  • Last Modified:

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
0
Richard Korts
Asked:
Richard Korts
2 Solutions
 
Dave BaldwinFixer 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
 
MurfurFull Stack DeveloperCommented:
SUBSTRING is for text fields, better to use a date function:

SELECT * FROM citrans WHERE YEAR( dtstamp ) = '2012'
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
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.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now