[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

send email reminder 3 days before deadline...

Posted on 2006-04-19
7
Medium Priority
?
280 Views
Last Modified: 2012-06-22
Hi all,

I have got a fieldname called suspenseDate.

This fieldname is usually given a value of  20 days.

This means that if an event occurs today, I have a suspensedate of 20 days to exercise an option.

An email program has been developed to send a reminder to the individual responsible for exercising this option. The email is supposed to go out 3 days before the 20-day deadline elapses, reminding him that he has 3 days left before the 20-day deadline for exercising an option is over.
Is there anything wrong with this code?

So far, no email has been sent even though there are instances where email should have been sent.

SELECT ID, fieldContactName, CONVERT(VarChar,SuspenseDate,101),CONVERT(VarChar,EntryDate,101)
FROM  [Activity]
WHERE CONVERT(VarChar,DateAdd(day,-17,getdate()),101) = CONVERT(VarChar,SuspenseDate,101)
AND receiptDate IS NULL AND Department = 'IT'
ORDER BY [Activity].ID DESC

Any ideas?
0
Comment
Question by:nigerman
  • 4
  • 3
7 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16486980
you have to specify the size of the varchar for the conversion, otherwise it will not work as expected:
WHERE CONVERT(VarChar(10),DateAdd(day,-17,getdate()),101) = CONVERT(VarChar(10),SuspenseDate,101)
0
 

Author Comment

by:nigerman
ID: 16487431
Thanks, Angel.

I thought that varchar has a variable length.

Therefore, value takes as much of the length as needed and discards the rest. This appears to be a better choice than char.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16487472
you are correct, but also varchar has a MAX size.

now, as you want to compare DATE and not DATE + TIME, you have to limit the string representation of the data value to the date part:

check out this:
select convert(varchar(20), getdate(), 101) as long_date_string, convert(varchar(10), getdate(), 101) as short_date_string

if you keep the time portion, the comparsion will not work as you expect it, as it compares also the time
0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 

Author Comment

by:nigerman
ID: 16487621
Ok, I think I got your point.

What you are saying is that if I don't truncate the varchar length for getdate(), it time portion of date value is added.

Essentially, I would be comparing:

04/19/2006 (suspensedate) with 04/19/2006 9:47:13 (getdate()).

This makes eminent sense.

Thank you!
0
 

Author Comment

by:nigerman
ID: 16487739
Well, in that case, what is wrong with using CHAR since it only has 10 characters?

like:

WHERE CONVERT(Char(10),DateAdd(day,-17,getdate()),101) = CONVERT(Char(10),SuspenseDate,101)

I am asking this because I have used this query too but no results.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 200 total points
ID: 16487832
>Well, in that case, what is wrong with using CHAR since it only has 10 characters?
there is no difference here, as the values will all be 10 chars long for the comparison.

just to ensure, the field SuspenseDate is of data type Date?!
0
 

Author Comment

by:nigerman
ID: 16487978
thank you!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question