在SQL Server中设置日期

在SQL Server中,如何将DATETIME"置于"第二/分钟/小时/日/年？

假设我的日期是2008-09-17 12:56:53.430,那么地板的输出应该是:

年:2008-01-01 00:00:00.000

月:2008-09-01 00:00:00.000

日:2008-09-17 00:00:00.000

时间:2008-09-17 12:00:00.000

会议纪要:2008-09-17 12:56:00.000

第二名:2008-09-17 12:56:53.000

Portman.. 98

关键是使用DATEADD和DATEDIFF以及适当的SQL时间间隔枚举.

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

请注意,当您按秒进行平铺时,如果使用0,则通常会出现算术溢出.因此,请选择一个保证低于您尝试放置的日期时间的已知值.

1> Portman..：

---

关键是使用DATEADD和DATEDIFF以及适当的SQL时间间隔枚举.

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

请注意,当您按秒进行平铺时,如果使用0,则通常会出现算术溢出.因此,请选择一个保证低于您尝试放置的日期时间的已知值.

2> Chris Wueste..：

---

在SQL Server中,这是一个小技巧:

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

您将DateTime转换为float,它将Date表示为整数部分,将Time表示为传递的一天的分数.切掉那个小数部分,然后把它再投回到DateTime,你就在那天的开始时有午夜.

这可能比所有DATEADD和DATEDIFF更有效.输入肯定更容易.

---

@Portman - 您的申请是否有基础,效率低15%？

---

cast to floor会损害性能,因为它会跳过任何日期时间索引.使用SQL 2008时,最好使用datediff函数或CAST([field] AS TIME)或CAST([field]作为DATE)

3> 小智..：

---

扩展Convert/Cast解决方案,在Microsoft SQL Server 2008中,您可以执行以下操作:

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

只需替换getdate()为日期时间的任何列.

此转换不涉及任何字符串.

这对于即席查询或更新是可以的,但对于密钥连接或大量使用的处理,最好在处理中处理转换或重新定义表以具有适当的密钥和数据.

在2005年,你可以使用凌乱的地板: cast(floor(cast(getdate() as float)) as datetime)

我不认为这也使用字符串转换,但我不能说比较实际效率与扶手椅估计.

4> Dan Atkinson..：

---

多年来我多次使用@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