仅 MS SQL 日期没有时间 [英] MS SQL Date Only Without Time

问题描述

问题

大家好，

很长一段时间以来，我一直在混淆使用 T-SQL 对 DateTime SQL 类型进行基本划分.本质上，我想将 DateTime 值设为 2008-12-1 14:30:12 并将其设为 2008-12-1 00:00:00.我们为报告运行的许多查询在 WHERE 子句中使用日期值，但我要么使用一天的开始和结束日期值并使用 BETWEEN，要么我找到其他方法.

目前我正在使用以下内容:<代码>WHERE CAST(CONVERT(VARCHAR, [tstamp], 102) AS DATETIME) = @dateParam

但是，这看起来有点笨拙.我希望会有更简单的东西CAST([tstamp] AS DATE)

网上有些地方推荐使用 DATEPART() 函数，但我最终得到了这样的结果:

<前><代码>WHERE DATEPART(year, [tstamp]) = DATEPART(year, @dateParam)AND DATEPART(month, [tstamp]) = DATEPART(month, @dateParam)AND DATEPART(day, [tstamp]) = DATEPART(day, @dateParam)

也许我过于关心一些小事，如果是这样，请告诉我.我只是想确保我正在写的东西尽可能高效.我想消除任何薄弱环节.

有什么建议吗?

谢谢，
C

解决方案

感谢大家的精彩反馈.很多有用的信息.我将围绕我们的函数进行更改，以消除运算符左侧的函数.尽管我们的大多数日期列不使用索引，但它可能仍然是一种更好的做法.

解决方案

那个对性能很不好，看看只有在数据库中，您才能通过更改几行代码获得 1000% + 改进

运算符左侧的函数不好

这是你需要做的

声明@d 日期时间选择@d = '2008-12-1 14:30:12'其中 tstamp >= dateadd(dd, datediff(dd, 0, @d)+0, 0)和 tstamp 

运行它看看它做了什么

select dateadd(dd, datediff(dd, 0, getdate())+1, 0)选择 dateadd(dd, datediff(dd, 0, getdate())+0, 0)

Question

Hello All,

I've had some confusion for quite some time with essentially flooring a DateTime SQL type using T-SQL. Essentially, I want to take a DateTime value of say 2008-12-1 14:30:12 and make it 2008-12-1 00:00:00. Alot of the queries we run for reports use a date value in the WHERE clause, but I either have a start and end date value of a day and use a BETWEEN, or I find some other method.

Currently I'm using the following: WHERE CAST(CONVERT(VARCHAR, [tstamp], 102) AS DATETIME) = @dateParam

However, this seems kinda clunky. I was hoping there would be something more simple like CAST([tstamp] AS DATE)

Some places online recommend using DATEPART() function, but then I end up with something like this:

WHERE DATEPART(year, [tstamp]) = DATEPART(year, @dateParam)
AND DATEPART(month, [tstamp]) = DATEPART(month, @dateParam)
AND DATEPART(day, [tstamp]) = DATEPART(day, @dateParam)

Maybe I'm being overly concerned with something small and if so please let me know. I just want to make sure the stuff I'm writing is as efficient as possible. I want to eliminate any weak links.

Any suggestions?

Thanks,
C

Solution

Thanks everyone for the great feedback. A lot of useful information. I'm going to change around our functions to eliminate the function on the left hand side of the operator. Although most of our date columns don't use indexes, it is probably still a better practice.

解决方案

that is very bad for performance, take a look at Only In A Database Can You Get 1000% + Improvement By Changing A Few Lines Of Code

functions on the left side of the operator are bad

here is what you need to do

declare @d datetime
select @d =  '2008-12-1 14:30:12'

where tstamp >= dateadd(dd, datediff(dd, 0, @d)+0, 0)
and tstamp < dateadd(dd, datediff(dd, 0, @d)+1, 0)

Run this to see what it does

select dateadd(dd, datediff(dd, 0, getdate())+1, 0)
select dateadd(dd, datediff(dd, 0, getdate())+0, 0)

这篇关于仅 MS SQL 日期没有时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！