Filter "Posting Date" by the SQL DateTime value for Time of '23:59:59.000'

Dynamics 365 Business Central/NAV Developers Forum
1

I need to filter G/L Entries to exclude Closing Dates.

I could do this by using the time from SQL where all closing entries have a time of ‘23:59:59.000’

The problem is, how can I get the time as stored in SQL?

I would normally use the “Source Code”

IF AnalysisView.“Exclude Closing Entries” THEN

GLEntry.SETFILTER(“Source Code”, ‘<>%1’, ‘CLSINCOME’);

However we have many language versions and the codes used in each vary quite a lot:

The SQL team use the time as a reliable means of filtering and I would like to do the same.

2

Sounds interesting Colin, but what’s wrong with the “good old” way to filter for closing dates on?

3

I have not found a method that works as yet, what do you suggest?

4

the field timestamp is no datetime field. it’s a hex value field used for versioning the record. but it’s always changed, when the record is modfied. so you can use it for triggering.

add a new field LastModified (type DateTime).

add following sql trigger to your table with sql mgmt tool. with that trigger the new field LastModified is updated with the current datetime, always when the record is modified. so you get the needed field for filtering.

USE []

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER [dbo].[Update_LastModified]

ON [dbo].[$]

AFTER INSERT,UPDATE

AS

IF (UPDATE(timestamp))

BEGIN

– SET NOCOUNT ON added to prevent extra result sets from

– interfering with SELECT statements.

SET NOCOUNT ON;

– Insert statements for trigger here

Update [$]

set [$].LastModified = GETDATE()

FROM inserted

WHERE inserted.No = [$].No

END

// No is the key field

5

Very impressive Jonathan, however, it is not the record timestamp, the Posting Date is a (within NAV) Date type field that the financial people love.

So things like “lastmodified” and so on have no meaning.

Each G/L Entry has data in the normal way including Date type fields such as Document Date & Posting Date.

The Document Date is perhaps the Vendors Invoice Date.

Thanks anyway,

Colin

6

This just to clarify my question.

I said

I could do this by using the time from SQL where all closing entries have a time of ‘23:59:59.000’

I should have said

I could do this by using the time part of the Posting Date from SQL where all closing entries have a time part of the Posting Date of ‘23:59:59.000’

Cheers,

Colin