CODEWITHSUNDEEP
sqlcoldfusion
Vicki
Vicki

Reputation: 1446

While updating date compare date with another field to update field

I have a button and when a user clicks it, the button sets the due_date in the database. Then I have another button that when it's clicked it sets the date_finished in the database.

I have one more field that needs to be completed and then the finished button is clicked. I am trying to figure out how to compare the date_finished to the date_due such that if the date is after the date_due(the next day) I want to update my Late field with a 'Y' or if the date_finished was on time or equal to the same day update my Late field with an 'N'.

I can not figure out how to compare the dates though in the same query.

enter image description here

<cfquery name="workComplete">
        UPDATE dbo.Work 
        SET date_finished = getDate(),
              <cfif date_finished > due_date>
                Late = <cfqueryparam value="Y" cfsqltype="cf_sql_varchar">
              <cfelse>
                Late = <cfqueryparam value="N" cfsqltype="cf_sql_varchar">
              </cfif>
        WHERE id = <cfqueryparam value="11" cfsqltype="cf_sql_integer">,          
    </cfquery>

Upvotes: 0

Views: 139

Answers (1)

Matt Busche
Matt Busche

Reputation: 14333

You would want to use a CASE statement to compare based on database values

    <cfquery name="workComplete">
      UPDATE dbo.Work 
      SET date_finished = getDate(),
      Late = CASE WHEN date_finished > due_date THEN 'Y' ELSE 'N' END
      WHERE id = <cfqueryparam value="11" cfsqltype="cf_sql_integer">,          
    </cfquery>

If date_finished is a value in ColdFusion you can output it at a ColdFusion value as well

    <cfquery name="workComplete">
      UPDATE dbo.Work 
      SET date_finished = getDate(),
      Late = CASE WHEN #form.date_finished# > due_date THEN 'Y' ELSE 'N' END
      WHERE id = <cfqueryparam value="11" cfsqltype="cf_sql_integer">,          
    </cfquery>

Upvotes: 1

Related Questions