1

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)

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?

Giorgi Nakeuri
  • 35,155
  • 8
  • 47
  • 75
Boone
  • 137
  • 3
  • 14

2 Answers2

1

May be this:

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

Even more short answer:

WHERE YEAR([Posting Date]) <> YEAR(DATEADD(ss, 1, [Posting Date]))
Giorgi Nakeuri
  • 35,155
  • 8
  • 47
  • 75
  • Unfortunately I'm getting the same results as my query that isn't working. – Boone May 29 '15 at 13:39
  • @Boone, yes there was a mistake, you need `OR`s – Giorgi Nakeuri May 29 '15 at 13:41
  • 3
    I suspect performance for this will be really bad as the query optimizer won't be able to use any indexes and will have to process each row. A better idea would be to create a table with exclusion dates for the next 50 years (50 rows only) and exclude these from the report, eg with `WHERE [Posting Date] NOT IN ...) – Panagiotis Kanavos May 29 '15 at 13:50
  • @PanagiotisKanavos, thank you for that advice. While my data is relatively small, it was very simple to implement and reduced my execution time from 11 seconds to 8 seconds. – Boone May 29 '15 at 14:15
0

Another thing which you can also do is something like this, which is a slight variation from what you have tried.

WHERE 1 = CASE 
        WHEN (
                DATEPART(MONTH, [POSTING DATE]) = 12
                AND DATEPART(DAY, [POSTING DATE]) = 31
                AND DATEPART(HOUR, [POSTING DATE]) = 23
                AND DATEPART(MINUTE, [POSTING DATE]) = 59
                AND DATEPART(SECOND, [POSTING DATE]) = 59
                )
            THEN 0
        ELSE 1
        END

In this scenario I just use a CASE to identify those rows which have the date the last second of the year and the evaluation of 1/0 does the rest and filters out the unwanted records.

Radu Gheorghiu
  • 20,049
  • 16
  • 72
  • 107