Solved

Syntax for recurring date

Posted on 2008-10-20
8
185 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:N R
[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 250 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 250 total points
ID: 22761544

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

Author Comment

by:N R
ID: 22768164
Scott, wouldn't that just add 20 days to the current date?
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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 11

Author Comment

by:N R
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 11

Author Comment

by:N R
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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

734 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