SQL筛选出任何年份的特定月份，日期和时间 [英] SQL Filter Out Specific Month, Day, and Time for Any Year

问题描述

Microsoft Dynamics导航使用 yyyy-12-31 23:59的 datetime ：59.000 来标识年度结算条目。我正在尝试编写一份报告，其中将包含所有这些条目。如果我明确使用

Microsoft Dynamics NAV uses the datetime of yyyy-12-31 23:59:59.000 to identify yearly closing entries. I am trying to write a report that will bring all of my entries, except for these. The query works fine for 2014 if I explicitly use a WHERE clause of

WHERE [Posting Date] <> '2014-12-31 23:59:59.000')

但是我需要查询才能使用任何一年。我尝试过：

But I need the query to work for any year. I tried:

WHERE (DATEPART(mm, [Posting Date]) <> 12) AND 
      (DATEPART(dd, [Posting Date]) <> 31) AND 
      (DATEPART(hh, [Posting Date]) <> 23) AND 
      (DATEPART(mi, [Posting Date]) <> 59) AND 
      (DATEPART(ss, [Posting Date]) <> 59)

但这过滤掉了12月 或一天<< c $ c> 31 或 23 的小时，等等...

But that filtered out everything that was in December or had a day of 31 or an hour of 23, etc...

是否有一种简单的方法来过滤给定的日期时间，但是年？

Is there a simple way to filter a given datetime, but with any year?

推荐答案

可能是这样的：

WHERE MONTH([Posting Date]) <> 12 OR
      DAY([Posting Date]) <> 31 OR
      CAST([Posting Date] AS TIME) <> CAST('23:59:59.000' AS TIME)

更简短的答案：

WHERE YEAR([Posting Date]) <> YEAR(DATEADD(ss, 1, [Posting Date]))

这篇关于SQL筛选出任何年份的特定月份，日期和时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！