Looking for direction to Build MySQL date query

Question

reference Date: 20.10.2015

step 1: need the max date from PayTO table > max(PayTO)

step 2: check if this max(PayTO) < reference Date (20.10.2015)

MenTBL
======
TZ   |   Fname
===========
11    |  ZZZ
22    |  XXX
33    |  CCC
44    |  VVV


PayTbl
======
TZ   |  PayTO
===========
11    |  01/01/2015
11    |  03/05/2015
11    |  06/01/2016
22    |  01/01/2015
22    |  01/10/2015
33    |  01/01/2015
33    |  01/12/2015
44    |  01/01/2015
44    |  04/05/2015
44    |  18/10/2015

The result should be that way:

22    |  01/10/2015  |  XXX
44    |  18/10/2015  |  VVV

Important note: PayTO is a string Field

recommend converting PayTO to a date field as that is what it stores and far easier to use in that way — amdixon
– amdixon, Commented Oct 19, 2015 at 6:43

amdixon · Accepted Answer · 2015-10-19 07:16:13Z

2

query

select p.TZ, 
date_format(max(str_to_date(p.PayTO, '%d/%m/%Y')), '%d/%m/%Y') as dt,
m.FName
from PayTbl p
inner join MenTbl m
on p.TZ = m.TZ
group by p.TZ, m.FName
having max(str_to_date(p.PayTO, '%d/%m/%Y'))
<      DATE('2015-10-20')
;

output

+----+------------+-------+
| TZ |     dt     | FName |
+----+------------+-------+
| 22 | 01/10/2015 | XXX   |
| 44 | 18/10/2015 | VVV   |
+----+------------+-------+

sqlfiddle

notes

due to the PayTO field type, have to use str_to_date. this will perform slower than the analogous query with date typed field

edited Oct 19, 2015 at 7:16

answered Oct 19, 2015 at 7:04

amdixon

3,8438 gold badges27 silver badges35 bronze badges

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

1 Comment

amdixon Over a year ago

no worries. only small recommendation is to develop a script to migrate your data to store it in date type ( then query simplifies even further )

Collectives™ on Stack Overflow

Looking for direction to Build MySQL date query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related