Skip to main content
K
kodemonki
Inspiring
September 22, 2008
Answered

Updating Dates w/CFQUERY

  • September 22, 2008
  • 8 replies
  • 611 views
I have a query that gets an enrollment id, term months and a launch date (of type date) from Oracle 8i. I'm trying to update a different table using CF 4.5

When I use CFQUERYPARAM I get CFQUERYPARAM data conversion error. When I use add_months() I get an invalid column name error.

I just want to update effective_end_date to term_months after launch.

Please help, thanks!

Here are ways I have tried it:
This topic has been closed for replies.
Correct answer Dan_Bracuk
In that first query, you can always add the add_months function result to your select clause and then you have the value you want.

Better yet, drop the column you are trying to update and just select what you need when you need it.

8 replies

K
kodemonkiAuthor
Inspiring
September 23, 2008
This was an excellent suggestion, thank you! (and it worked!)
D
Dan_BracukCorrect answer
Inspiring
September 22, 2008
In that first query, you can always add the add_months function result to your select clause and then you have the value you want.

Better yet, drop the column you are trying to update and just select what you need when you need it.
K
kodemonkiAuthor
Inspiring
September 22, 2008
I guess I should have laid out my code (I thought posting the whole thing would be too much.

query to get enrollment id, launch date and term months
loop over query
update query with top query's values
/loop

I have hashes because launch and term_months are variables from the first query result set.
D
Dan_Bracuk
Inspiring
September 22, 2008
why do you have octothorps around launch and term_months?
K
kodemonkiAuthor
Inspiring
September 22, 2008
This gives me the error "invalid number of arguments" (output displayed as add_months(2006-05-23 00:00:00, 36))
update dlr_enrollments
set effecitve_end_date = add_months(#launch#, #term_months#)
where enrollment_id = #enrollment_id#

If I put launch in quotes I get invalid column name error.
If I change add_months(#launch# to add_months(to_date('#launch#','mm/dd/yyyy') I also get invalid column name.

add_months(#month(launch)# || '/01/' || #year(launch)# yeilds even worse results.
D
Dan_Bracuk
Inspiring
September 22, 2008
use oracle's add_months function.
K
kodemonkiAuthor
Inspiring
September 22, 2008
term_months is an integer (number type in oracle)
launch is a date (date type in oracle)

For example, if a dealer enrolls in a five month program, I want the end date to be five months after the program is launched.
D
Dan_Bracuk
Inspiring
September 22, 2008
deleted
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices