blocks|key|701805|text|我通常是这样做的|type|unstyled|depth|inlineStyleRanges|entityRanges|data|701806|SELECT+*
FROM+MyTable
WHERE+CONVERT(VARCHAR,+MyTable.dateField,+101)+=+CONVERT(VARCHAR,+GETDATE(),+101)|code-block|syntax|javascript|701807|如果使用的是SQL+SERVER+2008，则可以执行以下操作：|701808|SELECT+*
FROM+MyTable
WHERE+CAST(MyTable.dateField+AS+DATE)+=+CAST(GETDATE()+AS+DATE)|701809|希望这能有所帮助|701810|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

I usually do

<pre><code>SELECT *
FROM MyTable
WHERE CONVERT(VARCHAR, MyTable.dateField, 101) = CONVERT(VARCHAR, GETDATE(), 101)
</code></pre>

if you are using SQL SERVER 2008, you can do

<pre><code>SELECT *
FROM MyTable
WHERE CAST(MyTable.dateField AS DATE) = CAST(GETDATE() AS DATE)
</code></pre>

Hope this helps

blocks|key|486130|text|在SQL+Server2008和更高版本中，可以将时间转换为Date，这会删除DateTime元素。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|486131|WHERE+Orders.OrderStatus+=+'Shipped'++
AND+Orders.ShipDate+>=+(cast(GETDATE()-6+as+date))++|code-block|syntax|javascript|486132|在SQL+Server+2005及更低版本中，您可以使用：|486133|WHERE+Orders.OrderStatus+=+'Shipped'++
AND+Orders.ShipDate+>=+DateAdd(Day,+Datediff(Day,0,+GetDate()+-6),+0)|486134|entityMap^0|U|4|13|8|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]|$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|W|8|@]|D|@]|E|$]]|$1|M|3|N|5|H|7|X|8|@]|D|@]|E|$I|J]]|$1|O|3|-4|5|6|7|Y|8|@]|D|@]|E|$]]]|P|$]]

In SQL Server 2008 and newer you can cast the <code>DateTime</code> to a <code>Date</code>, which removes the time element.

<pre><code>WHERE Orders.OrderStatus = 'Shipped' 
AND Orders.ShipDate &gt;= (cast(GETDATE()-6 as date)) 
</code></pre>

In SQL Server 2005 and below you can use:

<pre><code>WHERE Orders.OrderStatus = 'Shipped' 
AND Orders.ShipDate &gt;= DateAdd(Day, Datediff(Day,0, GetDate() -6), 0)
</code></pre>

blocks|key|486199|text|正如@BassamMehanni提到的，你可以在SQL+Server2008以后的版本中转换为日期...|type|unstyled|depth|inlineStyleRanges|entityRanges|data|486200|SELECT
++*
FROM
++yourTable
WHERE
++++++dateField+>=+CAST(GetDate()+-+6+AS+DATE)
++AND+dateField+<++CAST(GetDate()+%2B+1+AS+DATE)|code-block|syntax|javascript|486201|第二个条件实际上可以只是GetDate()，但我将这种格式作为Less+Than+DateX的一个示例来显示，以避免将dateField也转换为日期，从而极大地提高了性能。|offset|length|style|CODE|486202|如果你在2005年或以下，你可以使用这个...|486203|SELECT
++*
FROM
++yourTable
WHERE
++++++dateField+>=+DATEADD(DAY,+DATEDIFF(DAY,+0,+GetDate())+-+6,+0)
++AND+dateField+<++DATEADD(DAY,+DATEDIFF(DAY,+0,+GetDate())+%2B+1,+0)|486204|entityMap^0|0|0|C|9|V|F|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@$I|V|J|W|K|L]|$I|X|J|Y|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|Z|8|@]|9|@]|A|$]]|$1|O|3|P|5|D|7|10|8|@]|9|@]|A|$E|F]]|$1|Q|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|R|$]]

As @BassamMehanni mentioned, you can cast as DATE in SQL Server 2008 onwards...

<pre><code>SELECT
 *
FROM
 yourTable
WHERE
 dateField &gt;= CAST(GetDate() - 6 AS DATE)
 AND dateField &lt; CAST(GetDate() + 1 AS DATE)
</code></pre>

The second condition can actually be just <code>GetDate()</code>, but I'm showing this format as an example of <code>Less Than DateX</code> to avoid having to cast the dateField to a DATE as well, thus massively improving performance.

 

If you're on 2005 or under, you can use this...

<pre><code>SELECT
 *
FROM
 yourTable
WHERE
 dateField &gt;= DATEADD(DAY, DATEDIFF(DAY, 0, GetDate()) - 6, 0)
 AND dateField &lt; DATEADD(DAY, DATEDIFF(DAY, 0, GetDate()) + 1, 0)
</code></pre>

blocks|key|702008|text|这是我找到的最简单的东西|type|unstyled|depth|inlineStyleRanges|entityRanges|data|702009|--+Midnight+floor+of+current+date

SELECT+Convert(DateTime,+DATEDIFF(DAY,+0,+GETDATE()))|code-block|syntax|javascript|702010|DATEDIFF返回1900-1-1之前或自1900-1-1以来的整数天数，Convert+Datetime友好地在午夜将其返回到该日期。|702011|由于DateDiff返回一个整数，您可以使用add或subtract+days来获得正确的偏移量。|702012|SELECT+Convert(DateTime,+DATEDIFF(DAY,+0,+GETDATE())+%2B+@dayOffset)|702013|这不是四舍五入，这是truncating...But，我想这就是我们要问的。(为了舍入，添加一个和truncate...and，它也不是舍入的，那是天花板，但也很可能是你想要的。要真正舍入，请添加.5+(这行吗？)然后截断。|702014|事实证明，您可以将.5添加到GetDate()中，它就会按预期工作。|702015|--+Round+Current+time+to+midnight+today+or+midnight+tomorrow

SELECT+Convert(DateTime,+DATEDIFF(DAY,+0,+GETDATE()+%2B+.5))|702016|我在SQL+Server2008上做了所有的测试，但我认为这些功能也适用于2005年。|702017|entityMap^0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|Z|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|10|8|@]|9|@]|A|$E|F]]|$1|M|3|N|5|6|7|11|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|12|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|13|8|@]|9|@]|A|$E|F]]|$1|S|3|T|5|6|7|14|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|V|$]]

Here is the simplest thing I've found

<pre><code>-- Midnight floor of current date

SELECT Convert(DateTime, DATEDIFF(DAY, 0, GETDATE()))
</code></pre>

The DATEDIFF returns the integer number of days before or since 1900-1-1,
and the Convert Datetime obligingly brings it back to that date at midnight.

Since DateDiff returns an integer you can use add or subtract days to get the right offset.

<pre><code>SELECT Convert(DateTime, DATEDIFF(DAY, 0, GETDATE()) + @dayOffset)
</code></pre>

This isn't rounding this is truncating...But I think that is what is being asked.
(To round add one and truncate...and that's not rounding either, that the ceiling, but
again most likely what you want. To really round add .5 (does that work?) and truncate.

It turns out you can add .5 to GetDate() and it works as expected.

<pre><code>-- Round Current time to midnight today or midnight tomorrow

SELECT Convert(DateTime, DATEDIFF(DAY, 0, GETDATE() + .5))
</code></pre>

I did all my trials on SQL Server 2008, but I think these functions apply to 2005 as well.

blocks|key|702058|text|SELECT+getdate()|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|702059|结果:2012-12-1416:03:33.360|unstyled|702060|SELECT+convert(datetime,convert(bigint,+getdate()))|702061|结果2012-12-15+00:00:00.000|702062|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|N|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$B|C]]|$1|I|3|J|5|F|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|F|7|Q|8|@]|9|@]|A|$]]]|L|$]]

<pre><code>SELECT getdate()
</code></pre>

Result: 2012-12-14 16:03:33.360

<pre><code>SELECT convert(datetime,convert(bigint, getdate()))
</code></pre>

Result 2012-12-15 00:00:00.000

blocks|key|702071|text|试着用这个。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|702072|WHERE+Orders.OrderStatus+=+'Shipped'++
AND+Orders.ShipDate+>=+CONVERT(DATE,+GETDATE())|code-block|syntax|javascript|702073|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try using this.

<pre><code>WHERE Orders.OrderStatus = 'Shipped' 
AND Orders.ShipDate &gt;= CONVERT(DATE, GETDATE())
</code></pre>

blocks|key|486338|text|--
--+SQL+DATEDIFF+getting+midnight+time+parts+
--
SELECT+GETDATE()+AS+Now,+
+++Convert(DateTime,+DATEDIFF(DAY,+0,+GETDATE()))+AS+MidnightToday,
+++Convert(DateTime,+DATEDIFF(DAY,+-1,+GETDATE()))+AS+MidnightNextDay,
+++Convert(DateTime,+DATEDIFF(DAY,+1,+GETDATE()))+AS+MidnightYesterDay
go
Now+++++++++++++++++++MidnightToday++++++++++MidnightNextDay++++++++MidnightYesterDay+++++
+--------------------++---------------------++---------------------++---------------------+
+8/27/2014+4:30:22+PM++8/27/2014+12:00:00+AM++8/28/2014+12:00:00+AM++8/26/2014+12:00:00+AM+|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|486339|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>--
-- SQL DATEDIFF getting midnight time parts 
--
SELECT GETDATE() AS Now, 
 Convert(DateTime, DATEDIFF(DAY, 0, GETDATE())) AS MidnightToday,
 Convert(DateTime, DATEDIFF(DAY, -1, GETDATE())) AS MidnightNextDay,
 Convert(DateTime, DATEDIFF(DAY, 1, GETDATE())) AS MidnightYesterDay
go
Now MidnightToday MidnightNextDay MidnightYesterDay 
 -------------------- --------------------- --------------------- --------------------- 
 8/27/2014 4:30:22 PM 8/27/2014 12:00:00 AM 8/28/2014 12:00:00 AM 8/26/2014 12:00:00 AM 
</code></pre>

blocks|key|702118|text|你可以把时间四舍五入。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|702119|使用下面的ROUND将把它四舍五入到午夜。|offset|length|style|CODE|702120|WHERE+Orders.OrderStatus+=+'Shipped'++
AND+Orders.ShipDate+>++CONVERT(datetime,+(ROUND(convert(float,+getdate()-6.5),0)))|code-block|syntax|javascript|702121|entityMap^0|0|5|5|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@$D|Q|E|R|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|S|8|@]|9|@]|A|$K|L]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

You could round down the time.

Using <code>ROUND</code> below will round it down to midnight.

<pre><code>WHERE Orders.OrderStatus = 'Shipped' 
AND Orders.ShipDate &gt; CONVERT(datetime, (ROUND(convert(float, getdate()-6.5),0)))
</code></pre>

I am having a small problem with my SQL query. I'm using the GETDATE function, however, let's say I execute the script at 5PM, it will pull up records between 12/12/2011 5PM to 12/18/2011 5PM. How can I make it pull up records for the whole entire 12/12/2011 - 12/18/2011 basically ignore time.

My script:

<pre><code>WHERE Orders.OrderStatus = 'Shipped' 
AND Orders.ShipDate &gt; (GETDATE()-6) 
</code></pre>

Rounding SQL DateTime to midnight

SQL Server

我的SQL查询有一个小问题。我使用的是GETDATE函数，但是，假设我在下午5点执行脚本，它将在12/12/2011 5‘m到12/18/2011 5’m之间拉出记录我怎么才能让它把整个12/12/2011 - 12/18/2011的记录拉出来，基本上忽略时间。我的脚本：WHERE Orders.OrderStatus...

问将SQL DateTime舍入到午夜。
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问将SQL DateTime舍入到午夜。EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问将SQL DateTime舍入到午夜。
EN