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
Regards,
Marcelo Silva Santos
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
4 | |
3 | |
3 | |
3 | |
2 | |
2 | |
2 | |
2 | |
2 | |
2 |