CODEWITHSUNDEEP
mysqldate
anonamas
anonamas

Reputation: 241

extract month from mysql

How can I extract the month with a sql query in mysql. The date format is as follows: dd/mm/yyyy. 

MONTH(datecol) = $month // this does not work.

However if I change the date format to yyyy-mm-dd it works, but this is not an option.

Upvotes: 2

Views: 665

Answers (2)

spencer7593
spencer7593

Reputation: 108370

This expression will extract the portion of the string that appears between the first and second forward slashes.

SUBSTRING_INDEX(SUBSTRING_INDEX( '25/07/2013' ,'/',2),'/',-1)

will return '07'

To get this converted to a numeric, add zero to it, 

SUBSTRING_INDEX(SUBSTRING_INDEX( '25/07/2013' ,'/',2),'/',-1) + 0

will return a numeric value of 7

NOTES

This expression will work with any string value, not just strings representing a valid date in the specified format. For example:

SUBSTRING_INDEX(SUBSTRING_INDEX( 'dd/mm/yyyy' ,'/',2),'/',-1)

will return 'mm'

If only one slash is found in the string, this will return the portion following the slash. If no slashes are found in the string, this will return the entire string.

If your intent is to validate that the string represents a valid date value, in the specified format, then the STR_TO_DATE() function can be used to convert the string to a DATE. The return from that expression can be used as an argument to any of the MySQL functions that take a DATE argument, such as the MONTH() function in your sample question.

Upvotes: 3

Joni
Joni

Reputation: 111219

You can parse a date in any format into an SQL date value with str_to_date. For example:

select month(str_to_date('25/07/2013', '%d/%m/%Y'));

Upvotes: 4

Related Questions