Can't update date field with a variable

[John_Allred]

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

[John_Allred]

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.

[EddieLotter · Accepted solution]

Use the CreateOdbcDate() function.

[John_Allred]

I should take a picture of my face with my best "DOH!" expression on it.

Thanks so much, Eddie!

~john

[EddieLotter]

You're welcome. 🙂