将 SQLServer 日期时间转换为较短的日期格式

我在 SQL Server 中有一个 datetime列，它提供了类似于这个 10/27/2010 12:57:49 pm的数据，我想查询这个列，但是只需要让 SQL Server 返回日期、月份和年份即可。2010 10 27之类的。

我应该研究哪些功能？

我应该尝试转换到另一个日期数据类型? 还是仅仅将其转换为字符串？

424893 次浏览

小开

最佳答案

Have a look at CONVERT. The 3rd parameter is the date time style you want to convert to.

e.g.

SELECT CONVERT(VARCHAR(10), GETDATE(), 103) -- dd/MM/yyyy format

小开

In addition to CAST and CONVERT, if you are using Sql Server 2008, you can convert to a date type (or use that type to start with), and then optionally convert again to a varchar:

declare @myDate date
set @myDate = getdate()
print cast(@myDate as varchar(10))

output:

2012-01-17

小开

If you need the result in a date format you can use:

Select Convert(DateTime, Convert(VarChar, GetDate(), 101))

小开

Try this:

print cast(getdate() as date )

小开

Just add date keyword. E.g. select date(orderdate),count(1) from orders where orderdate > '2014-10-01' group by date(orderdate);

orderdate is in date time. This query will show the orders for that date rather than datetime.

Date keyword applied on a datetime column will change it to short date.

小开

With SQL Server 2005, I would use this:

select replace(convert(char(10),getdate(),102),'.',' ')

Results: 2015 03 05

小开

For any versions of SQL Server: dateadd(dd, datediff(dd, 0, getdate()), 0)

小开

The shortest date format of mm/dd/yy can be obtained with:

Select Convert(varchar(8),getdate(),1)

小开

If you have a datetime field that gives the results like this 2018-03-30 08:43:28.177

Proposed: and you want to change the datetime to date to appear like 2018-03-30

cast(YourDateField as Date)

小开

The original DateTime field : [_Date_Time]

The converted to Shortdate : 'Short_Date'

CONVERT(date, [_Date_Time]) AS 'Short_Date'