0

I want to update a record's DateUpdated field each time the user updates the record.

I thought I should do it like this:

<cfquery>
UPDATE
dbo.MyTable
SET DateUpdated = <cfqueryparam value="GetDate()" />
</cfquery>

I don't know if this correct. There's no cfsqltype attribute value for a function. In which case, do I just do this

<cfquery>
    UPDATE
    dbo.MyTable
    SET DateUpdated = GetDate()
</cfquery>
volume one
  • 6,800
  • 13
  • 67
  • 146
  • This will update every row in the table, BTW – James A Mohler Feb 14 '15 at 22:06
  • @JamesAMohler you're right, its just example code – volume one Feb 14 '15 at 22:52
  • 1
    You are confusing CF and database functions. Expressions within the cfqueryparam are evaluated by the CF server *first*. CF then sends the evaluated string of SQL on to your database for processing. Since `getDate()` is a SQL Server function, CF knows nothing about it. Meaning you cannot use it inside cfqueryparam. You can only use *CF* functions, such as `now()` (cont'd) – Leigh Feb 14 '15 at 22:54
  • 2
    Using a function makes no difference. The `cfsqltype` applies to the single value *returned* by the function. If the function returns a datetime object (like `now()` does), you use `cf_sql_timestamp` (date and time) OR `cf_sql_date` (date only) depending on whether or not you wish to include the time portion. However, as Carl pointed out, if you can also use the database function `getDate()` instead. In which case you do not need cfqueryparam at all. – Leigh Feb 14 '15 at 22:54
  • 2
    RE: *Correct way to pass in a SQL function as a value* To clarify and answer your question, a **database** function is not a "value" . It is a sql command that must be evaluated by your database. Wrapping it in cfqueryparam prevents that from happening. So the answer is: do not use cfqueryparam on **database** functions. Your second example is the correct syntax. – Leigh Feb 15 '15 at 04:19

1 Answers1

4

The first one won't work because you parameterized the SQL function name, so it will pass "GetDate()" as a literal string. However, if you replaced "GetDate()" with "#Now()#" it should work, since you are passing the value from a ColdFusion function.

The second one should work as-is though.

Carl Von Stetten
  • 1,149
  • 8
  • 13