在使用分析时，您可能需要大量的日期\时间截断。所以我做了一个小函数来帮助整理:

CREATE FUNCTION TRUNC_DATE
(
    @datetime datetime, -- datetime to be truncated
    @level VARCHAR(10)  -- truncation level: year, month, day, hour and minute
)
RETURNS DATETIME
AS
BEGIN

    IF (UPPER(@level) = 'YEAR')
       RETURN DATEADD(YEAR,   DATEDIFF(YEAR, 0, @datetime), 0)
    ELSE IF (UPPER(@level) = 'MONTH')
        RETURN DATEADD(MONTH,   DATEDIFF(MONTH, 0, @datetime), 0)
    ELSE IF(UPPER(@level) = 'DAY')
       RETURN DATEADD(DAY,   DATEDIFF(DAY, 0, @datetime), 0)
    ELSE IF (UPPER(@level) = 'HOUR')
       RETURN DATEADD(HOUR,   DATEDIFF(HOUR, 0, @datetime), 0)
    ELSE IF (UPPER(@level) = 'MINUTE')
       RETURN DATEADD(MINUTE,   DATEDIFF(MINUTE, 0, @datetime), 0)

    RETURN @datetime
END
GO

计算函数(用you列更改GETDATE()):

SELECT DBO.TRUNC_DATE(GETDATE(), 'YEAR')   YEAR;
SELECT DBO.TRUNC_DATE(GETDATE(), 'MONTH')  YEAR_MONTH;
SELECT DBO.TRUNC_DATE(GETDATE(), 'DAY')    YEAR_MONTH_DAY;
SELECT DBO.TRUNC_DATE(GETDATE(), 'HOUR')   YEAR_MONTH_DAY_HOUR;
SELECT DBO.TRUNC_DATE(GETDATE(), 'MINUTE') YEAR_MONTH_DAY_HOUR_MINUTE;

输出:

你可以这样做(SQL 2008):

@SomeDate date = getdate()

select @SomeDate

2009-05-28

对于那些来到这里寻找一种方法来截断一个DATETIME字段小于一天，例如每分钟，你可以使用这个:

SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) + (FLOOR((CAST(GETDATE() AS FLOAT) - FLOOR(CAST(GETDATE() AS FLOAT))) * 1440.0) + (3.0/86400000.0)) / 1440.0 AS DATETIME)

因此，如果今天是2010-11-26 14:54:43.123，那么这个将返回2010-11-26 14:54:00.000。

要更改它的运行时间间隔，请将1440.0替换为一天中的间隔数，例如:

24hrs          =   24.0  (for every hour)
24hrs / 0.5hrs =   48.0  (for every half hour)
24hrs / (1/60) = 1440.0  (for every minute)

(总是在末尾加上。0以隐式转换为浮点数。)

对于那些想知道(3.0/86400000)在我的计算中是用来做什么的人来说，SQL Server 2005似乎并没有准确地从FLOAT转换为DATETIME，所以这增加了3毫秒。

您还可以使用Substring从datetime变量中提取日期，并强制转换回datetime将忽略时间部分。

declare @SomeDate datetime = '2009-05-28 16:30:22'
SELECT cast(substring(convert(varchar(12),@SomeDate,111),0,12) as Datetime) 

同样，你也可以访问datetime变量的一部分，并将它们合并为一个截断日期的构造，就像这样:

SELECT cast(DATENAME(year, @Somedate) + '-' + 
       Convert(varchar(2),DATEPART(month, @Somedate)) + '-' +
       DATENAME(day, @Somedate) 
       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

当我不得不这样做时，我在网上找到的片段是:

 dateadd(dd,0, datediff(dd,0, YOURDATE))
 e.g.
 dateadd(dd,0, datediff(dd,0, getDate()))