在SQL Server 2008中截断日期时间值(如删除小时、分钟和秒)的最佳方法是什么?
例如:
declare @SomeDate datetime = '2009-05-28 16:30:22'
select trunc_date(@SomeDate)
-----------------------
2009-05-28 00:00:00.000
在SQL Server 2008中截断日期时间值(如删除小时、分钟和秒)的最佳方法是什么?
例如:
declare @SomeDate datetime = '2009-05-28 16:30:22'
select trunc_date(@SomeDate)
-----------------------
2009-05-28 00:00:00.000
当前回答
CONVERT(DATE, <yourdatetime>) or CONVERT(DATE, GetDate()) or CONVERT(DATE, CURRENT_TIMESTAMP)
其他回答
Oracle:
TRUNC(SYSDATE, 'MONTH')
SQL服务器:
DATEADD(DAY, - DATEPART(DAY, DateField) + 1, DateField)
可以类似地用于从日期截断分钟或小时。
选择cast(floor(cast(getdate() as float))作为日期时间 参考这个:http://microsoftmiles.blogspot.com/2006/11/remove-time-from-datetime-in-sql-server.html
仅适用于SQL Server 2008
CAST(@SomeDateTime AS Date)
然后将其转换回datetime
CAST(CAST(@SomeDateTime AS Date) As datetime)
SQL Server 2022有DATETRUNC
DECLARE @d datetime2 = '2021-12-08 11:30:15.1234567';
SELECT 'Year', DATETRUNC(year, @d);
SELECT 'Quarter', DATETRUNC(quarter, @d);
SELECT 'Month', DATETRUNC(month, @d);
SELECT 'Week', DATETRUNC(week, @d); -- Using the default DATEFIRST setting value of 7 (U.S. English)
SELECT 'Iso_week', DATETRUNC(iso_week, @d);
SELECT 'DayOfYear', DATETRUNC(dayofyear, @d);
SELECT 'Day', DATETRUNC(day, @d);
SELECT 'Hour', DATETRUNC(hour, @d);
SELECT 'Minute', DATETRUNC(minute, @d);
SELECT 'Second', DATETRUNC(second, @d);
SELECT 'Millisecond', DATETRUNC(millisecond, @d);
SELECT 'Microsecond', DATETRUNC(microsecond, @d);
Year 2021-01-01 00:00:00.0000000
Quarter 2021-10-01 00:00:00.0000000
Month 2021-12-01 00:00:00.0000000
Week 2021-12-05 00:00:00.0000000
Iso_week 2021-12-06 00:00:00.0000000
DayOfYear 2021-12-08 00:00:00.0000000
Day 2021-12-08 00:00:00.0000000
Hour 2021-12-08 11:00:00.0000000
Minute 2021-12-08 11:30:00.0000000
Second 2021-12-08 11:30:15.0000000
Millisecond 2021-12-08 11:30:15.1230000
Microsecond 2021-12-08 11:30:15.1234560
https://learn.microsoft.com/en-us/sql/t-sql/functions/datetrunc-transact-sql?view=sql-server-ver16
这个查询应该会给出与Oracle中的trunc(sysdate)相同的结果。
SELECT *
FROM your_table
WHERE CONVERT(varchar(12), your_column_name, 101)
= CONVERT(varchar(12), GETDATE(), 101)
希望这能有所帮助!