Solved

Syntax for recurring date

Posted on 2008-10-20
8
180 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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

757 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

20 Experts available now in Live!

Get 1:1 Help Now