mysql substr not working like php substring

Question

my date format is like

2010-11-15 04:28:31

i want to pick just 2010-11-15 instead of 2010-11-15 04:28:31 ,

using MYSQL query ,

SELECT SUBSTR(my_date,0,6) FROM TBL ,

This query retrieving just empty value ,

How to form substr function in mysql QUERY ,

GolezTrol · Accepted Answer · 2010-11-20 13:30:54Z

7

MySQL uses 1-based strings, so the first character is 1, while in php the first character is 0.

Better is to use the DATE function to get the date-part only from a date time field. That is, if your field is indeed a date time field, not a (var)char field.

If you want to display your date in a specific format you can also use

DATE_FORMAT(my_date, '%Y-%m-%d')

edited Nov 20, 2010 at 13:30

answered Nov 20, 2010 at 13:17

GolezTrol

116k19 gold badges186 silver badges215 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Yahel Over a year ago

Slightly confusing to say "assumes the first character is 1". What you mean is that MySQL uses 1-based indexing, not 0-based indexing that is typically found in C-like languages.

GolezTrol Over a year ago

You're right, I had trouble finding the correct word. I edited the answer. Thanks!

Collectives™ on Stack Overflow

mysql substr not working like php substring

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related