Enterprise Resource Planning Blogs by Members
Gain new perspectives and knowledge about enterprise resource planning in blog posts from community members. Share your own comments and ERP insights today!
cancel
Showing results for 
Search instead for 
Did you mean: 
Former Member
2,448

How to setup Alerts using a field DocTime

Was creating some alerts and came across some situations. First the query that will trigger the alert should contain some filters, which DocType, DocStatus, DocDate so far so good. The problem arose when I needed to be more specific and put a limit on time. In recent versions of SAP Business One is a field marketing documents called DocTime then made ​​the following combination.

SELECT T0.DocEntry, T0.DocStatus, T0.DocDate, T0.CardCode, T0.CardName, T0.ObjType

FROM OINV T0 WHERE T0.DocStatus = 'O' AND T0.ObjType = '13'

AND  CONVERT(VARCHAR,A.CREATEDATE,103) = CONVERT(VARCHAR,GETDATE(), 103)

AND (CONVERT(varchar, datepart(hour, getdate())) + '' + CONVERT(varchar, datepart(minute, getdate())) - T0.DocTime) <= 15

Before that it was need create a user field to record the time and then use in a comparison. Today it is possible to do this directly with field Native systems. And this comparison can be done directly by two ways, it will depend on whether you're with SQL2008 or SQL2012.

SELECT T0.DocEntry, T0.DocStatus, T0.DocDate, T0.CardCode, T0.CardName, T0.ObjType

FROM OINV T0 WHERE T0.DocStatus = 'O' AND T0.ObjType = '13'

AND  CONVERT(VARCHAR,A.CREATEDATE,103) = CONVERT(VARCHAR,GETDATE(), 103)

AND  CONVERT(CHAR,(T0.U_HOUR - T0.U_REMINDER),108)>= CONVERT (CHAR,GETDATE(),108)

In SQL2012 to use SELECT FORMAT(SYSDATETIME(), 'HHmm')

In SQL2008 to use SELECT CONVERT(varchar, datepart(hour, getdate())) + '' + CONVERT(varchar, datepart(minute, getdate())

..compared with the DocTime

Comments and suggestions are welcome!

References:

http://www.sql-server-helper.com/sql-server-2012/format-function-vs-convert-function.aspx

http://blog.sqlauthority.com/2012/07/16/sql-server-2012-functions-format-and-concat-an-interesting-u...

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/a0c16e74-974b-2d10-ec98-a2c00675b...


Regards,

Marcelo Silva Santos

3 Comments
Labels in this area