如何在Teradata中使用DATE格式

Question

我有以下结果集。

purchase_date              invoice_no
2020-05-06 13:14:54.000000 I2516211
2020-05-05 20:30:08.000000 I2515992
2020-05-05 02:00:26.000000 I2515763
2020-05-03 22:00:51.000000 I2515504

我需要得到如下结果集。(显示每天有多少发票(

2020-05-03  1
2020-05-06  1
2020-05-05  2

不知道如何在sql中使用日期格式函数。(Im使用teradata(

Answer 1

您可以使用to_char():将值转换为字符串

select to_char(purchase_date, 'YYYY-MM-DD'), count(*)
from t
group by to_char(purchase_date, 'YYYY-MM-DD')
order by to_char(purchase_date, 'YYYY-MM-DD');

您也可以将值转换为日期：

select cast(purchase_date as date), count(*)
from t
group by cast(purchase_date as date)
order by cast(purchase_date as date);

Answer 2

这与Gordon的答案相同，但重复使用了列别名(Teradata功能(：

SELECT 
CAST(purchase_date AS DATE) AS purchase_date, 
COUNT(*) AS purchase_count
FROM mytable
GROUP BY purchase_date
ORDER BY purchase_date;