Question

请问用SQL查询时间时怎么去掉查询结果中的“时分秒”，只要精确到“天”。

我写的查询语句是：
select distinct orderdate as "672"
from d_orderdetail inner join D_Order on D_Order.orderid=d_orderdetail.orderid
where (productid=672)
and (orderdate between '2004-07-01' and '2005-06-30')
order by orderdate
查询的结果如上图所示，现在想要将查询的结果显示为“yyyy-mm-dd”，请问语句该怎么写？

Answer 1

select distinct Convert(varchar(10),orderdate,120) as "672"
from d_orderdetail inner join D_Order on D_Order.orderid=d_orderdetail.orderid
where (productid=672)
and (Convert(varchar(10),orderdate,120) between '2004-07-01' and '2005-06-30')
order by orderdate

这样就可以了

Answer 2

select distinct to_char(orderdate,'yyyy-mm-dd') as "672"

Answer 3

Select CONVERT(varchar(30),getdate(),102) 这个可以转换

Answer 4

STR_TO_DATE((DATE_FORMAT(time,'%Y-%m-%d')),'%Y-%m-%d')

Answer 5

select distinct DATE_FORMAT(orderdate ,'%Y-%m-%d') as "672"

from d_orderdetail inner join D_Order on D_Order.orderid=d_orderdetail.orderid
where (productid=672)
and (DATE_FORMAT(orderdate ,'%Y-%m-%d') between '2004-07-01' and '2005-06-30')
order by orderdate