Syntax for recurring date
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 :-) .
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 :-) .
ASKER
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
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
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??]
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??]
ASKER
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
update debtorpaymentplan
set FirstPayDate = dateadd(m, 1, FirstPayDate)
where getdate() > FirstPayDate
OK, makes sense. Thanks for the follow up.
ASKER