SQL使用date_trunc按周分组时如何去除结果中的时间戳后缀

date_trunc返回结果为时间戳类型，默认携带时分秒后缀，你只需要将返回值转换为日期类型，或者按指定格式格式化输出即可实现需求，不同数据库的实现语法略有差异，以下是常用数据库的修改方案：

• PostgreSQL/Redshift/Hive等
  直接强转为日期类型即可：

  select date_trunc('week', date_created)::date as wk, count(transaction_id)
  from table 
  group by 1
  

  如果需要固定输出MM-DD-YY格式的字符串，可使用格式化函数：

  select to_char(date_trunc('week', date_created), 'MM-DD-YY') as wk, count(transaction_id)
  from table 
  group by 1
  

• MySQL
  用date()函数剥离时间部分：

  select date(date_trunc('week', date_created)) as wk, count(transaction_id)
  from table 
  group by 1
  

  自定义格式输出：

  select date_format(date_trunc('week', date_created), '%m-%d-%y') as wk, count(transaction_id)
  from table 
  group by 1
  

• SQL Server
  用cast转换为日期类型：

  select cast(date_trunc('week', date_created) as date) as wk, count(transaction_id)
  from table 
  group by 1
  

  自定义格式输出：

  select format(date_trunc('week', date_created), 'MM-dd-yy') as wk, count(transaction_id)
  from table 
  group by 1
  

内容的提问来源于stack exchange，提问作者Chris90