MySQL DateTime 中的删除时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/493223/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Drop Time in DateTime
提问by Oliver S
I have a problem where my table stores the date as
我有一个问题，我的表将日期存储为
2009-01-10 10:00:00.000
and I have another table which stores the date as
我还有另一个表将日期存储为
2009-01-10 12:00:00.000
I know they are not the same but the dates are, is there away in SQL to easily drop the time and keep just the dates for comparison? Thanks.
我知道它们不一样，但日期是一样的，SQL 中是否可以轻松删除时间并只保留日期进行比较？谢谢。
回答by SQLMenace
Run this
运行这个
SELECT DATEADD(dd, DATEDIFF(d, 0, Getdate()), 0)
change Getdate() to your column name to strip the date
将 Getdate() 更改为您的列名以去除日期
BTW if you are doing a comparison it is better to do >= and < since it will perform better
顺便说一句，如果您要进行比较，最好执行 >= 和 < 因为它会表现得更好
回答by Kunal Kabra
You can use this syntax:-
您可以使用以下语法：-
CAST(date_field AS DATE) as column_name
CAST(date_field AS DATE) as column_name
回答by StingyHyman
Look here
看这里
You want the first one.
你想要第一个。
SELECT (CAST(FLOOR(CAST(GETDATE() as FLOAT)) AS DateTime))
Do not use any conversions to varchar, they are slow.
不要使用任何到 varchar 的转换，它们很慢。
EDIT: @feihtthief - Here is a way to populate a table of dates (with 0 time) from a temp numbers table that is FAST. Edit it to save the numbers table if you want. They are handy as well.
编辑：@feihtthief - 这是一种从快速的临时数字表中填充日期表（时间为 0）的方法。如果需要，编辑它以保存数字表。它们也很方便。
DECLARE @DaysFromGoLive int
SET @DaysFromGoLive = (SELECT (DATEDIFF(dd,'10/01/2007',GETDATE()) + 1)) /* Days from go live is the starting date of the table */
SELECT TOP 10950 --30 years of days
IDENTITY(INT,1,1) as N
INTO #Numbers
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
CREATE TABLE [dbo].[TableOfDates](
[fld_date] [datetime] NOT NULL,
CONSTRAINT [PK_TableOfDates] PRIMARY KEY CLUSTERED
(
[fld_date] ASC
)WITH FILLFACTOR = 99 ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO
dbo.TableOfDates
SELECT
DATEADD(dd,nums.n - @DaysFromGoLive,CAST(FLOOR(CAST(GETDATE() as FLOAT)) as DateTime)) as FLD_Date
FROM #Numbers nums
SELECT MIN(FLD_Date) FROM dbo.TableOfDates
SELECT MAX(FLD_Date) FROM dbo.TableOfDates
DROP TABLE #Numbers
回答by John Milazzo
If you're using MS SQL Server 2008 you could also change to the new DATE datatype instead of using DATETIME.
如果您使用的是 MS SQL Server 2008，您还可以更改为新的 DATE 数据类型，而不是使用 DATETIME。
回答by AlexCuse
Here's one way:
这是一种方法：
declare @d datetime
set @d = getdate()
select dateadd(d, datediff(day, 0, @d), 0)
回答by AlexCuse
Converting to varchar is SLOW. Don't do it. The Datediff solution is fastest, and if you're comparing with a date you don't even have to use dateadd (or alternately convert to datetime) since the integer will implicitly compare with a date no problem, as in:
转换为 varchar 很慢。不要这样做。Datediff 解决方案是最快的，如果您要与日期进行比较，您甚至不必使用 dateadd（或转换为 datetime），因为整数将隐式地与日期进行比较，没问题，如下所示：
WHERE SomeDate = DateDiff(d, 0, GetDate())
Mark Brittingham's suggestion on a join between two tables is pretty good. And to compare to just a single date, as suggested, use :
Mark Brittingham 关于两个表之间连接的建议非常好。并按照建议与单个日期进行比较，请使用：
WHERE A.Dt >= @SomeDate AND A.Dt < @SomeDate + 1
回答by MAgnus
A SQL CLR function like this should to it:
像这样的 SQL CLR 函数应该：
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlDateTime StripTime(SqlDateTime date)
{
return date.IsNull ? SqlDateTime.Null : new SqlDateTime(date.DayTicks, 0);
}
};
回答by K. Brian Kelley
All of these solutions (with the exception of the SQL Server 2008 datatype post) are row-based solutions. They'll work okay in small table situations, but they will churn in very large table cases. If you aren't on 2008, do you have the option of storing the date as separate integer fields for year, month, and day or as a calculation from some fixed date?
所有这些解决方案（除了 SQL Server 2008 数据类型 post）都是基于行的解决方案。它们在小桌子情况下可以正常工作，但在非常大的桌子情况下它们会流失。如果您不在 2008 年，您是否可以选择将日期存储为单独的整数字段，用于年、月和日，或者作为某个固定日期的计算？
回答by Thorsten
In Oracle you could do a trunc(DateTime) which gets rid of the time component. (There are variation like trunc(DateTime, 'MONTH') which gives the first date of the month.) Not sure if this is standard SQL, but I'm sure there would similar functions in other databases.
在 Oracle 中，您可以执行 trunc(DateTime) 来摆脱时间组件。（有像 trunc(DateTime, 'MONTH') 这样的变体，它给出了当月的第一个日期。）不确定这是否是标准 SQL，但我确定在其他数据库中会有类似的函数。
This should be alot faster than working with a conversion function.
这应该比使用转换函数快得多。
回答by Adam
My favorite way to only display (and compare) on the date is to use:
我最喜欢只显示（和比较）日期的方法是使用：
convert(char(10),getdate(),101)
where getdate() can be your datetime value.
其中 getdate() 可以是您的日期时间值。
i believe it converts to the format of MM/DD/YYYY
我相信它会转换为 MM/DD/YYYY 的格式
