Can't update date field with a variable

Question

I have a DB with a date field that I need to update periodically. I'd like to set a date on the same template where I insert the record.

I run my insert query, then run a select query for maxID to get the data row. I set a session variable with a calculated date, and I want to "set" that date in the field with an update query. In the following code, setting the value as "2021-09-17" will update the field, as I desire. But, if I try to use my session variable for the update, it doesn't work. I've tried every variation on syntax I can conceive of, and nothting works.

After the form action, debugging on my page shows that the variable exists and holds a value. Is there a chance that it doesn't get set before the update query fires?

    <!--- Set ExpireDate column --->
    <cfquery datasource="#APPLICATION.dsn#">
        UPDATE tbl_user
        <!---SET ExpireDate = "#session.ExpireDate#"--->
        SET ExpireDate = "2021-09-17"
        WHERE UserID = '#session.UserID#'
        </cfquery>

I have no doubt the solution is simple, but it escapes me.

~john

EddieLotter · Accepted Answer

Use the CreateOdbcDate() function.

John_Allred · Answer

Out of curiosity, I inserted the following code between my insert query and update query to test for the contents of the variable.

    <cfif len(trim(session.expiredate))>
        <cflocation url="/index.cfm?value=#session.expiredate#">
    </cfif>

This is my result: "http://localhost:8502/index.cfm?value=17-Sep-21"

So, indeed, the variable has a value.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded