sql join query date format-CodePudding

I'm trying to make nice table view (in grafana) of my trading profits where I group my trades by date.

SELECT
close_date AS "Date",
(SELECT COUNT(*) FROM trades WHERE close_profit > 0 AND c2.close_date = close_date) AS Wins,
(SELECT COUNT(*) FROM trades WHERE close_profit < 0 AND c2.close_date = close_date) AS Losses,
(SELECT AVG(close_profit * 100) FROM trades WHERE c2.close_date = close_date) AS "Avg",
(SELECT SUM(close_profit_abs) FROM trades WHERE c2.close_date = close_date) AS "Profit"
FROM trades c2 WHERE close_date IS NOT NULL GROUP BY close_date

This will create table list of trades, but it wont group trades by date because original "close_date" is in form of "2021-09-20 11:11:12.954078 0000 UTC".

How can I format the close_date so that there is only date (2021-09-20) and use it in joined queries? I have tried this:

SELECT
date(close_date) AS "Date",
.....

Which changes the close_date to date as I wanted but then grouping wont work.

I tried to change every close_date to date(close_date) but then the whole syntax breaks.

SELECT
date(close_date) AS "Date",
(SELECT COUNT(*) FROM trades WHERE close_profit > 0 AND c2.date(close_date) = date(close_date)) AS Wins,
(SELECT COUNT(*) FROM trades WHERE close_profit < 0 AND c2.date(close_date) = date(close_date)) AS Losses,
(SELECT AVG(close_profit * 100) FROM trades WHERE c2.date(close_date) = date(close_date)) AS "Avg",
(SELECT SUM(close_profit_abs) FROM trades WHERE c2.date(close_date) = date(close_date)) AS "Profit"
FROM trades c2 WHERE date(close_date) IS NOT NULL GROUP BY date(close_date)

The problem is :

c2.date(close_date)

How can I format the datetime here ?

c2.close_date (2021-09-20 11:11:12.954078 0000 UTC) -> c2.date(close_date) (2021-09-20) ??

Thank you for helping. Im not sql expert in any means so my terminology might be "little bit off".

CodePudding user response：

You can convert your date using CAST() & CONVERT() Function

Read more on this function CONVERT() & CAST()

SELECT CONVERT(varchar(10),'2021-09-20 11:11:12.954078  0000 UTC',105) AS [Date]

CodePudding user response：

Please Refer this code

SELECT
            CONVERT(varchar(10),close_date,105) AS "Date",
            (SELECT COUNT(*) FROM trades WHERE close_profit > 0 AND CONVERT(varchar(10),c2.close_date,105) = CONVERT(varchar(10),close_date,105)) AS Wins,
            (SELECT COUNT(*) FROM trades WHERE close_profit < 0 AND CONVERT(varchar(10),c2.close_date,105) = CONVERT(varchar(10),close_date,105)) AS Losses,
            (SELECT AVG(close_profit * 100) FROM trades WHERE CONVERT(varchar(10),c2.close_date,105) = CONVERT(varchar(10),close_date,105)) AS "Avg",
            (SELECT SUM(close_profit_abs) FROM trades WHERE CONVERT(varchar(10),c2.close_date,105) = CONVERT(varchar(10),close_date,105)) AS "Profit"
FROM trades c2 
WHERE CONVERT(varchar(10),close_date,105) IS NOT NULL 
GROUP BY CONVERT(varchar(10),close_date,105)