Mysql Query data between date on joined table

Question

Am trying to query data from two joined tables with a date as criteria using the query below but it brings nothing, can someone please show me what I did wrong.

SELECT Journal_T.GL_ID as Akaunti
      ,coa_t.gl_name_vc
      ,SUM(Amount_NU) as YTD
FROM  Journal_T 
      JOIN coa_t 
         ON journal_t.gl_id = coa_t.gl_id
WHERE CAST(date_dt AS DATE) BETWEEN '25/08/2017' AND '25/08/2017' 
      AND bs_category_vc='Rev'
GROUP BY coa_t.gl_name_vc
      ,Journal_T.GL_ID

are you sure on such cast as date? is high dependent on locale (country) active on mysql server — Jacek Cz, Aug 26 '17 at 12:49
Can you post some data, specifically what date_dt looks like. Preferably on a record you know should appear in the list — twoleggedhorse, Aug 26 '17 at 12:58
So just tried this and CAST AS DATE returns Null. You should use STR_TO_DATE as @vladatr has suggested — twoleggedhorse, Aug 26 '17 at 13:16
This work for me: select Journal_T.GL_ID as Akaunti,coa_t.gl_name_vc,sum(Amount_NU) as YTD from Journal_T JOIN coa_t on journal_t.gl_id = coa_t.gl_id where date(date_dt) BETWEEN '2017-08-25' AND '2017-08-25' AND bs_category_vc='Rev' GROUP by coa_t.gl_name_vc,Journal_T.GL_ID — Sayd Fuad, Aug 26 '17 at 13:21

score 1 · Accepted Answer · answered Aug 26 '17 at 13:04

1

ISO 8601 Date format would be '2017-08-26'. Try this query to see default Date format on your server:

select STR_TO_DATE("26/08/2017", '%d/%m/%Y')

Then you can convert it, for example to '%Y-%m-%d' format:

DATE_FORMAT(STR_TO_DATE("26/08/2017", '%d/%m/%Y'), '%Y-%m-%d')

answered Aug 26 '17 at 13:04

vladatr

616
6
15

@vladatr Thanks, I changed how I store date in database to Y-m-d and it worked. – Sayd Fuad Aug 26 '17 at 13:14

score 0 · Answer 2 · answered Aug 26 '17 at 13:10

0

I think you can try out this query :

SELECT Journal_T.GL_ID as Akaunti,coa_t.gl_name_vc,sum(Amount_NU) as YTD FROM Journal_T LEFT JOIN coa_t on journal_t.gl_id = coa_t.gl_id WHERE DATE_FORMAT(date_dt,'%d-%m-%Y') BETWEEN '25/08/2017' AND '25/08/2017' AND bs_category_vc='Rev' GROUP by coa_t.gl_name_vc,Journal_T.GL_ID

answered Aug 26 '17 at 13:10

Kishan

773
4
10

this didn't work for me – Sayd Fuad Aug 26 '17 at 13:17

Mysql Query data between date on joined table

2 Answers2