CODEWITHSUNDEEP
phpmysqldatetime
Justin
Justin

Reputation: 2530

Query Database for a specific Month of birthdays

I am trying to do a query against the database where I have contacts that have birthdays in a date format such as (1980-03-13) (Year-Month-Date). But I would like to query my database where all birthdays within the (03) month. How is this possible?

If anyone could shed some light that would be greatly appreciated.

PS. I am using PHP to do my queries with MySQL.

UPDATE: MySQL Table CONTACTS has a (DOB field) format = (date).

Upvotes: 1

Views: 2860

Answers (5)

S To
S To

Reputation: 93

Answer given by "Nick Rolando" worked for me.

Here is how I solved my same problem as yours:

SELECT date_of_birth FROM customer_id
WHERE EXTRACT(MONTH FROM date_of_birth) = 3;

Using the "WHERE" clause, the "EXTRACT" function does all the work by defining the "MONTH" attribute in the parentheses and knows how to only pull data which contains the month: 3

ALSO the date of birth column needs to have the proper date format, such as: "1999-03-29"

(BTW this worked for me on PostgreSQL 12)

Upvotes: 0

Piskvor left the building
Piskvor left the building

Reputation: 92762

You could do all kinds of string manipulation on this data, but it will be sloooow. I suggest keeping the date in a DATE column, plus the month as a separate INT column. This will duplicate a part of the data, but will allow you to query efficiently (esp. if you index the month). The reason: string operations are comparatively slow, and function calls can't be indexed.

Upvotes: -1

cuj74
cuj74

Reputation: 25

You could use SELECT MONTH('2008-02-03') to get the month number, replace the date with your column.

Upvotes: 2

Nick Rolando
Nick Rolando

Reputation: 26177

you can use the EXTRACT() function. In the WHERE clause:

WHERE EXTRACT(MONTH FROM dateColumn) = 3

Upvotes: 2

jenson-button-event
jenson-button-event

Reputation: 18961

select * from table
where Month(column) = 3

if its a string

select * from table
    where Month(cast(column as datetime)) = 3

Upvotes: 3

Related Questions