CODEWITHSUNDEEP
phpmysql
Syahmi Roslan
Syahmi Roslan

Reputation: 110

how to show date but data in my database type is datetime?

i want the output to show the date. but in the same time i want to filter by time.

SELECT CAST(t.req_date as Date) as Date, 
SUM(t.Transaction)+SUM(r.Request) as allTransaction, 
SUM(t.Success)+SUM(r.RequestSuccess) as allSuccess,  t.Transaction, 
t.Success, r.Request, r.RequestSuccess 
FROM (select req_date, count(*) as transaction, sum(t.status = 0) as success
from transfer_tx_201503 as t WHERE CAST(t.req_date as Time) >='00:00:00' AND 
CAST(t.req_date as Time) <= '23:00:00'
group by req_date desc) as t JOIN
(select req_date, count(*) as Request, SUM(r.status = 0) as RequestSuccess
from request_tx_201503 as r WHERE CAST(r.req_date as Time) >='00:00:00' AND 
CAST(r.req_date as Time) <= '23:00:00' 
group by req_date desc) as r
ON CAST(t.req_date as Date) = CAST(r.req_date as Date)
GROUP BY CAST(`date` as Date) desc

Upvotes: 0

Views: 36

Answers (2)

Syahmi Roslan
Syahmi Roslan

Reputation: 110

i have find my own solution. i use cast() to convert the data type.

SELECT CAST(t.req_date as Date) as Date, SUM(t.Transaction)+SUM(r.Request) as  
allTransaction, SUM(t.Success)+SUM(r.RequestSuccess) as allSuccess,    
t.Transaction, t.Success, r.Request, r.RequestSuccess 
FROM 
(select req_date, count(DISTINCT no_a) as Transaction, sum(t.status = 0) as Success
from transfer_tx_201503 as t WHERE CAST(t.req_date as time)  BETWEEN '00:00:00' AND '$searchterm'
group by CAST(req_date as Date) desc) as t 
JOIN
(select req_date, count(DISTINCT no_a) as Request, SUM(r.status = 0) as RequestSuccess
from request_tx_201503 as r WHERE CAST(r.req_date as time) BETWEEN '00:00:00' AND '$searchterm' 
group by CAST(req_date as Date) desc) as r
ON CAST(t.req_date as date) = CAST(r.req_date as date)
GROUP BY Date desc

Upvotes: 0

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

The most simple way would be to use date function on datetime column.

mysql> select date(now());
+-------------+
| date(now()) |
+-------------+
| 2015-03-30  |
+-------------+
1 row in set (0.00 sec)

Upvotes: 1

Related Questions