How to get data between two dates with month/date in query

Databases
1

I am not able to query between two dates. I have few rows inserted into the database having 10-25-2014, 08-15-2012 etc. as work_anniversary filed value. Now I want to query between the current & next month to get the employees who have work anniversary. How can i achieve this in mySql? It’s saved as string not date format. Also the year is not necessary in the query just the month and date.

2

Change the format :wink:

Can you give an example?

3

Sorry, I don’t have the permission to change the format.

Example: Suppose there are 50 employees in the table, and 10 of them have joined in SEP/OCT. And the joining date is saved like 10-25-2012, 09-15-2010 etc. Now I want to fetch those employees who have joined in SEP/OCT.

4

can you please try something similar to this –

5

yeah, I tried this. But the problem is ‘meta_value’ has ‘10-25-2012’ as value. So it returns data associated with this specific year. But i want data of specific months like SEP/OCT etc. So it will fetch all the data in those specific month of any year.

6

Use r937’s solution, taking only the first 5 characters from meta_value (dd/mm)

7

I used this, but not working

SELECT post_id FROM postmeta WHERE meta_key=‘_ct_work_anniversary’ AND STR_TO_DATE(CONCAT(LEFT(meta_value,5),‘/2000’), ‘%m/%d/%Y’) BETWEEN ‘2000-10-25’ AND ‘2000-09-15’

8

Is there anything wrong with this query?

9

Could you elaborate? What does “not working” mean?

And btw, if your data is stored with - (10-10-2015) then you might want to add the year with a - as well instead of a /

10

It’s working now. Thanks to all, for your replies :grinning:

11

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.