?
Solved

Syntax for recurring date

Posted on 2008-10-20
8
Medium Priority
?
187 Views
Last Modified: 2010-05-19
I'm trying to figure out how to update a date I guess.  I have a table called DebtorPaymentPlan and there is a field in there called firstpaydate.  This is fine when the date is say November 20th.  But after that passes and now we are in December and I pull from that table I don't want the due date to still be November 20th I want it to update to the next month.  The day will always be the 20th but the months need to update.
0
Comment
Question by:Nathan Riley
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 1000 total points
ID: 22761356
update t
set datefield = dateadd(m, 1, datefield)
from tablename t
where datefield = 'somedate'
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1000 total points
ID: 22761544

SELECT CAST(CONVERT(CHAR(6), GETDATE(), 112) + '20' AS DATETIME)
0
 
LVL 12

Author Comment

by:Nathan Riley
ID: 22768164
Scott, wouldn't that just add 20 days to the current date?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 22772101
Nope :-) :-) .

I truncated the current date to just yyyymm (since I specificed CHAR(6)) then concatenated the *character* string '20' to it to force day 20 -- the final string being yyyymm20, where 20 will be the dd. So, it just forces day 20 of the current month :-) .
0
 
LVL 12

Author Comment

by:Nathan Riley
ID: 22780281
Hey Scott I can open another question, but just something quick:

CAST(CONVERT(CHAR(6), GETDATE(), 112) + '20' AS DATETIME)

is giving me this:

Dec 20 2008 12:00AM

All that is fine but I don't need the time on there.  I tried to change your code to:
CAST(CONVERT(CHAR(6), GETDATE(), 101) + '20' AS DATETIME)

Dec 20 2020 12:00AM
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 22781327
The time is always present in a datetime column.

However, we can choose a format that will not show the time.  For example:

SELECT CONVERT(VARCHAR10), CAST(CONVERT(CHAR(6), GETDATE(), 112) + '20' AS DATETIME), 101)


[Btw, I don't see how chapmandew's post could have solved your issue. Did I miss something??]
0
 
LVL 12

Author Comment

by:Nathan Riley
ID: 22786295
Well it actually helped b/c I used both of your code. Yours helped by giving me the 20th of the current month for the bills, his changed the due dates up a month after the SP ran for next months run with this:

update debtorpaymentplan
set FirstPayDate = dateadd(m, 1, FirstPayDate)
where getdate() > FirstPayDate
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 22787038
OK, makes sense.  Thanks for the follow up.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

743 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