Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kalyformin
Starting Member
15 Posts |
 Posted - 2007-10-11 : 12:43:12
|
| I'm quite new to SQL Server and I have a pretty naive question.I have a table called Company that has a field called Renewal date. I have a task that needs to be run on the first of every month to gather all companies that have Renewal Dates coming up in the next 180 days. The Renewal Date is a datetime field in Sql Server. Is there a way I can have this accomplished.Any help is greatly appreciated.Thanks, |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-10-11 : 12:55:34
|
| [code]Where Renewal_date = dateadd(day, datediff(day, 0, dateadd(day, 180, getdate())), 0)[/code]Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
kalyformin
Starting Member
15 Posts |
Posted - 2007-10-11 : 14:06:48
|
| Thank you so much for the reply.I just had one more question. The Renewal Date for most companies is the first of every month. If we schedule our batch job on the 26th of each month, will this query handle dates for the next 180 days of the Renewal date? Put it simply, I just want to run the job on a particular date and have the query pick up companies that have renewals coming up in the next 180 days beginning the first of that month. Is this possible at all? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-11 : 15:09:22
|
| You could "round up to the next month" and then do the "180 days" thingie.But I always run-scared when I see this type of specification.Have you though about the potential "edge conditions" of 30 / 31 day months?February?Leap years?Non-leap centuries?Sounds to me like it would be better to have columns for:Last reminder triggered-dateNext reminder trigger-dateLast reminder sent dateWhich reminder was it? (First, Tickler, Final [which won't send any more])Kristen |
|
|
|
|
|
|
Date Manipulation in SQL Server