Hilmy Nur Ramdhani Hilmy Nur Ramdhani - 6 months ago 10
MySQL Question

Less equal than specific date query not working on equal date

I have this query:

SELECT COUNT(a.No_Registrasi) as Jumlah, b.Nama_Negara, a.Tanggal_Reg
FROM tb_registrasi a
JOIN tb_negara_tujuan b
ON a.ID_Negara = b.ID_Negara
WHERE a.Tanggal_Reg >= '2016-02-01' AND a.Tanggal_Reg <= '2016-03-01'
GROUP BY b.Nama_Negara


The result is:

result

But when im change to:

SELECT COUNT(a.No_Registrasi) as Jumlah, b.Nama_Negara, a.Tanggal_Reg
FROM tb_registrasi a
JOIN tb_negara_tujuan b
ON a.ID_Negara = b.ID_Negara
WHERE a.Tanggal_Reg >= '2016-02-01' AND a.Tanggal_Reg <= '2016-02-29' //this one
GROUP BY b.Nama_Negara


It didnt show any result, what im trying is to select data from between 2 different dates, but when when there's data on the date at the end of the month, it didnt show the data. say i have 5 data that registered to date 2016-04-30, when im selecting data from 2016-04-01 to 2016-04-30 it didnt show any result.

I hope you guys understand what i mean, thanks in advance.

Answer

Apply DATE Function to columns

WHERE DATE(a.Tanggal_Reg) >= '2016-02-01' AND DATE(a.Tanggal_Reg) <= '2016-02-29'

So your query will be:

SELECT COUNT(a.No_Registrasi) as Jumlah, b.Nama_Negara, a.Tanggal_Reg
FROM tb_registrasi a
JOIN tb_negara_tujuan b
   ON a.ID_Negara = b.ID_Negara
WHERE DATE(a.Tanggal_Reg) >= '2016-02-01' AND DATE(a.Tanggal_Reg) <= '2016-02-29' //this one
GROUP BY b.Nama_Negara