tableCId Time7
1 2013-07-18 09:10:10
2 2013-07-18 18:10:10
3 2013-07-01 18:41:31
4 2013-07-01 11:00:31
5 2013-07-01 09:10:10
我想要这样一个效果:查询tableC数据库表后,列出所有的日期(只包含年月日),并且按照时间从远到近Time2
2013-07-01
2013-07-18 请问这个语句应该怎么写?
1 2013-07-18 09:10:10
2 2013-07-18 18:10:10
3 2013-07-01 18:41:31
4 2013-07-01 11:00:31
5 2013-07-01 09:10:10
我想要这样一个效果:查询tableC数据库表后,列出所有的日期(只包含年月日),并且按照时间从远到近Time2
2013-07-01
2013-07-18 请问这个语句应该怎么写?
select distinct convert(varchar(10),time7,120) as Time2 from tableC
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-08-02 14:43:04
-- Version:
-- Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64)
-- Jun 10 2013 20:09:10
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[tableC]
if object_id('[tableC]') is not null drop table [tableC]
go
create table [tableC]([Id] int,[Time7] datetime)
insert [tableC]
select 1,'2013-07-18 09:10:10' union all
select 2,'2013-07-18 18:10:10' union all
select 3,'2013-07-01 18:41:31' union all
select 4,'2013-07-01 11:00:31' union all
select 5,'2013-07-01 09:10:10'
--------------开始查询--------------------------select DISTINCT CONVERT(VARCHAR(10),[time7],23) from [tableC] ORDER BY CONVERT(VARCHAR(10),[time7],23)
----------------结果----------------------------
/*
----------
2013-07-01
2013-07-18*/
select distinct convert(varchar(10),time7,120) as Time2 from tableC order by convert(varchar(10),time7,120)
FROM tableC
GROUP BY CONVERT(CHAR(10), Time7, 120)
ORDER BY Time2
from tableC
order by 1