在SQL server中输入日期

In SQL Server, how do I "floor" a DATETIME to the second/minute/hour/day/year?

在SQL Server中，如何将日期时间设置为秒/分钟/小时/天/年?

Let's say that I have a date of 2008-09-17 12:56:53.430, then the output of flooring should be:

假设我有一个日期为2008-09-17 12:56:53.430，那么地板的输出应该是:

Year: 2008-01-01 00:00:00.000
年:2008-01-01 00:00:00.000
Month: 2008-09-01 00:00:00.000
月:2008-09-01 00:00:00.000
Day: 2008-09-17 00:00:00.000
天:2008-09-17 00:00:00.000
Hour: 2008-09-17 12:00:00.000
小时:2008-09-17 12:00:00.000
Minute: 2008-09-17 12:56:00.000
分钟:2008-09-17 12:56:00.000
Second: 2008-09-17 12:56:53.000
第二:2008-09-17 12:56:53.000

9 个解决方案

#1

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

关键是使用DATEADD和DATEDIFF以及适当的SQL timespan枚举。

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.

请注意，当您使用秒级地板时，如果使用0，通常会得到一个算术溢出。因此，选择一个已知的值，该值一定要低于您要设置的datetime。

#2

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

在SQL Server中有一个小技巧:

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.

您将DateTime转换为一个浮点数，它表示日期作为整数部分，时间是通过的一天的小数部分。把那十进制的部分剪掉，然后把它转换回DateTime，这样你就可以在当天开始的时候看到午夜了。

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

这可能比所有DATEADD和DATEDIFF更有效。打字当然更容易。

#3

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

扩展到转换/强制转换解决方案，在Microsoft SQL Server 2008中，您可以执行以下操作:

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

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

只需将getdate()替换为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)

在2005年，您可以使用messier floor: cast(floor(cast) (getdate()作为float))作为datetime)

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

我认为这也不使用字符串转换，但我无法比较实际的效率和不切实际的估计。

#4

I've used @Portman's answer many times over the years as a reference when flooring dates and have moved its working into a function which you may find useful.

这些年来，我多次使用@Portman的答案作为地板上日期的参考，并将其应用于一个你可能会觉得有用的功能。

I make no claims to its performance and merely provide it as a tool for the user.

我不主张它的性能，只把它作为用户的工具。

I ask that, if you do decide to upvote this answer, please also upvote @Portman's answer, as my code is a derivative of his.

我问你，如果你决定支持这个答案，也请支持@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

Usage:

用法:

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

#5

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

CONVERT()函数也可以这样做，这取决于您使用的样式。

#6

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

可惜它不是Oracle，或者您可以使用trunc()或to_char()。

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

但是，我在SQL Server上遇到了类似的问题，并使用了CONVERT()和DateDiff()方法，这里引用了这些方法

#7

There are several ways to skin this cat =)

有几种方法可以剥猫皮=)

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

#8

DateAdd along with DateDiff can help to do many different tasks. For example, you can find last day of any month as well can find last day of previous or next month.

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

Source

源

#9

-2

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

由于PostgreSQL也是一个“SQL服务器”，我将提到它

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)

#1