CODEWITHSUNDEEP
mysqlsql
MCIT Trends
MCIT Trends

Reputation: 281

MySQL Date subtract from Month & Date Format

I have a silly question that I want to find no of days within curdate() and next 30 days, from MySQL column "increment_date" formatted as DD-MON (10-Nov, 15-Nov etc...)

I tried to get values as 8, 13 from that dates while comparing curdate()

tbl_officer

+--------+-----------+----------------+
| in_num | last_name | increment_date |
+--------+-----------+----------------+
|      1 | AA        | 10-Nov         |
|      2 | BB        | 12-Nov         |
|      3 | CC        | 20-Dec         |
|      4 | DD        | 22-Dec         |
+--------+-----------+----------------+

I used the following code to do that

select last_name 
  from tbl_officer
 where increment_date>= DATE(curdate()) 
   and increment_date<= DATE_SUB(DATE(curdate()), INTERVAL 31 DAY)

But the code outs empty result. What may be going wrong ? Can anyone help me ?

Upvotes: 0

Views: 389

Answers (3)

Bilal Siddiqui
Bilal Siddiqui

Reputation: 3629

EDIT:

STR_TO_DATE function to be used to convert string data in different format into a date value.

Your table data doesn't has year value so we need to append the current year on the fly, for eg:

SELECT * FROM tbl_officer where STR_TO_DATE(CONCAT(increment_date, '-2019'), "%d-%b-%Y")
BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 31 DAY);

So here is how I would complete it:

SELECT * FROM tbl_officer where STR_TO_DATE(CONCAT(increment_date, '-', YEAR(NOW())), "%d-%b-%Y") 
BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 31 DAY);

Upvotes: 2

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65323

You can use str_to_date() function with "%d-%b-%Y" pattern after combining with the current year(assuming you stay within the current year) :

select count(*) as "Number of Days"
  from tbl_officer
 where str_to_date(concat(increment_date,'-',year(curdate())),"%d-%b-%Y")
       between curdate() and curdate() + interval 30 day;

 Number of Days
 --------------
 2

Demo

Upvotes: 0

V&#233;race
V&#233;race

Reputation: 908

I think you want something like this (fiddle):

Create and populate your table:

CREATE TABLE tbl_officer
(
  in_num INTEGER NOT NULL,
  last_name VARCHAR (25) NOT NULL,
  increment_date VARCHAR (10) NOT NULL
);

INSERT INTO tbl_officer VALUES (1, 'AA', '10-Nov'), (2, 'BB', '12-Nov'), (3, 'CC', '20-Dec'),
(4, 'DD', '22-Dec'), (5, 'XX', '15-Nov');  -- XX added for testing

Then run this SQL (there are a couple of intermediary steps in the fiddle):

SELECT last_name 
FROM tbl_officer
WHERE STR_TO_DATE(CONCAT(increment_date, '-2019'), "%d-%M-%Y") >= CURDATE() 
AND   STR_TO_DATE(CONCAT(increment_date, '-2019'), "%d-%M-%Y") <= DATE_ADD(CURDATE(), INTERVAL 1 MONTH);

Result:

last_name
       AA
       BB
       XX

Upvotes: 0

Related Questions