Click here to Skip to main content
15,867,985 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
When an email is sent for approval purpose and no action is taken for 3 days then on 3rd day the system should automatically send an reminder email. this should happen every 3 days until action is taken

What I have tried:

I have tried this:
select * from [APPROVAL_HISTORY] where APPROVAL_TYPE = 'EXP_APRV' and APPROVAL_KEY = 'EXP-2115' and
convert(varchar, dateadd(day, 3,ACTION_DATE), 1) = convert(varchar, getdate(), 1) and ACTION_CODE in ('submitted' , 'in_progress')
but i can select only for 3 days after that also if no action is taken i need to continue the process
Posted
Updated 31-Oct-22 1:23am

1 solution

Start by getting rid of the convert(varchar, SOME DATE, 1) statements - there are much better ways to compare two datetime values whilst ignoring the time part.

In this case, you want to know whether the difference between the action date and the current date is a multiple of 3 days. For that, you'll want to use the modulus operator:
% (Modulus) (Transact-SQL) - SQL Server | Microsoft Learn[^]

Eg:
SQL
... and (DateDiff(day, ACTION_DATE, GetDate()) % 3) = 0 ...
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900