Solved

Syntax for recurring date

Posted on 2008-10-20
8
182 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
  • 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:ScottPletcher
ScottPletcher 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
 
LVL 69

Expert Comment

by:ScottPletcher
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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:ScottPletcher
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:ScottPletcher
ID: 22787038
OK, makes sense.  Thanks for the follow up.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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

929 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now