Solved

Syntax for recurring date

Posted on 2008-10-20
8
184 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: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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
backup and restore 21 30
tools to scan a SQL server's problem 14 28
Transact SQL - Frequency of Length of Distinct Values 3 26
SQL 2008 R2 problem with DB 9 16
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

821 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