在 SQL 服务器中加入一个日期

The key is to use DATEADD and DATEDIFF along with the appropriate SQL timespan enumeration.

declare @datetime datetime;
set @datetime = getdate();
select @datetime;
select dateadd(year,datediff(year,0,@datetime),0);
select dateadd(month,datediff(month,0,@datetime),0);
select dateadd(day,datediff(day,0,@datetime),0);
select dateadd(hour,datediff(hour,0,@datetime),0);
select dateadd(minute,datediff(minute,0,@datetime),0);
select dateadd(second,datediff(second,'2000-01-01',@datetime),'2000-01-01');
select dateadd(week,datediff(week,0,@datetime),-1); --Beginning of week is Sunday
select dateadd(week,datediff(week,0,@datetime),0); --Beginning of week is Monday

Note that when you are flooring by the second, you will often get an arithmetic overflow if you use 0. So pick a known value that is guaranteed to be lower than the datetime you are attempting to floor.

The CONVERT() function can do this as well, depending on what style you use.

Too bad it's not Oracle, or else you could use trunc() or to_char().

But I had similar issues with SQL Server and used the CONVERT() and DateDiff() methods, as referenced here

In SQL Server here's a little trick to do that:

SELECT CAST(FLOOR(CAST(CURRENT_TIMESTAMP AS float)) AS DATETIME)

You cast the DateTime into a float, which represents the Date as the integer portion and the Time as the fraction of a day that's passed. Chop off that decimal portion, then cast that back to a DateTime, and you've got midnight at the beginning of that day.

This is probably more efficient than all the DATEADD and DATEDIFF stuff. It's certainly way easier to type.

Since PostgreSQL is also a "SQL Server", I'll mention

date_trunc()

Which does exactly what you're asking gracefully.

For example:

select date_trunc('hour',current_timestamp);
date_trunc
------------------------
2009-02-18 07:00:00-08
(1 row)

Expanding upon the Convert/Cast solution, in Microsoft SQL Server 2008 you can do the following:

cast(cast(getdate() as date) as datetime)

Just replace getdate() with any column which is a datetime.

There are no strings involved in this conversion.

This is ok for ad-hoc queries or updates, but for key joins or heavily used processing it may be better to handle the conversion within the processing or redefine the tables to have appropriate keys and data.

In 2005, you can use the messier floor: cast(floor(cast(getdate() as float)) as datetime)

I don't think that uses string conversion either, but I can't speak to comparing actual efficiency versus armchair estimates.

多年来，我已经多次使用 @ Portman 的回答作为地板铺设日期的参考，并将其工作方式转移到一个你可能会发现有用的函数中。

我没有要求它的性能，只是提供它作为一个工具给用户。

我要求，如果你决定投票支持这个答案，请也支持 @ Portman 的回答，因为我的代码是他的导数。

IF OBJECT_ID('fn_FloorDate') IS NOT NULL DROP FUNCTION fn_FloorDate
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_FloorDate] (
@Date DATETIME = NULL,
@DatePart VARCHAR(6) = 'day'
)
RETURNS DATETIME
AS
BEGIN
IF (@Date IS NULL)
SET @Date = GETDATE();


RETURN
CASE
WHEN LOWER(@DatePart) = 'year' THEN DATEADD(YEAR, DATEDIFF(YEAR, 0, @Date), 0)
WHEN LOWER(@DatePart) = 'month' THEN DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0)
WHEN LOWER(@DatePart) = 'day' THEN DATEADD(DAY, DATEDIFF(DAY, 0, @Date), 0)
WHEN LOWER(@DatePart) = 'hour' THEN DATEADD(HOUR, DATEDIFF(HOUR, 0, @Date), 0)
WHEN LOWER(@DatePart) = 'minute' THEN DATEADD(MINUTE, DATEDIFF(MINUTE, 0, @Date), 0)
WHEN LOWER(@DatePart) = 'second' THEN DATEADD(SECOND, DATEDIFF(SECOND, '2000-01-01', @Date), '2000-01-01')
ELSE DATEADD(DAY, DATEDIFF(DAY, 0, @Date), 0)
END;
END

用法:

DECLARE @date DATETIME;
SET @date = '2008-09-17 12:56:53.430';


SELECT
@date AS [Now],--2008-09-17 12:56:53.430
dbo.fn_FloorDate(@date, 'year') AS [Year],--2008-01-01 00:00:00.000
dbo.fn_FloorDate(default, default) AS [NoParams],--2013-11-05 00:00:00.000
dbo.fn_FloorDate(@date, default) AS [ShouldBeDay],--2008-09-17 00:00:00.000
dbo.fn_FloorDate(@date, 'month') AS [Month],--2008-09-01 00:00:00.000
dbo.fn_FloorDate(@date, 'day') AS [Day],--2008-09-17 00:00:00.000
dbo.fn_FloorDate(@date, 'hour') AS [Hour],--2008-09-17 12:00:00.000
dbo.fn_FloorDate(@date, 'minute') AS [Minute],--2008-09-17 12:56:00.000
dbo.fn_FloorDate(@date, 'second') AS [Second];--2008-09-17 12:56:53.000

剥这只猫的皮有几种方法

select convert(datetime,convert(varchar,CURRENT_TIMESTAMP,101))

DateAdd 和 DateDiff 可以帮助完成许多不同的任务。例如，您可以找到任何月份的最后一天，也可以找到上个月或下个月的最后一天。

----Last Day of Previous Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
LastDay_PreviousMonth
----Last Day of Current Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))
LastDay_CurrentMonth
----Last Day of Next Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0))
LastDay_NextMonth

来源