Highlighted

Help with handling CF/SQL Date Arithmatic?

New Here ,
Nov 30, 2016

Copy link to clipboard

Copied

Hello

There is an order entry date: order_create_date.

I am able to display the aging by: #DateDiff("d", thequery.order_create_date, nowtime)#

I now need to stop the aging clock ticking with: order_onhold_date,

And resume the aging clock with: order_offhold_date

So it would be something like #DateDiff("d", thequery.order_create_date, nowtime)# minus #DateDiff("d",thequery.order_onhold_date,thequery.order_offhold_date)#

?

Any help would be appreciated- Thanks

That's correct, only you will need to take into account whether or not the order was placed on hold, and if so, whether or not it is still on hold.

I'm going to assume you have data integrity rules in place to prevent the on-hold dates from preceding the order date, that way you don't have to worry about it in code.

What you want to do is something like this:

<cfset nDaysOld = dateDiff('d', thequery.order_create_date, nowtime)>

<cfif thequery.order_onhold_date neq ''>

  <cfif thequery.order_offhold_date eq ''>

    <cfset nDaysOnHold = dateDiff('d', thequery.order_onhold_date, nowtime)>

  <cfelse>

    <cfset nDaysOnHold = dateDiff('d', thequery.order_onhold_date, order_offhold_date)>

  </cfif>

  <cfset nDaysOld -= nDaysOnHold>

</cfif>

Age: #nDaysOld#

Cheers

Eddie

TOPICS
Advanced techniques

Views

311

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Help with handling CF/SQL Date Arithmatic?

New Here ,
Nov 30, 2016

Copy link to clipboard

Copied

Hello

There is an order entry date: order_create_date.

I am able to display the aging by: #DateDiff("d", thequery.order_create_date, nowtime)#

I now need to stop the aging clock ticking with: order_onhold_date,

And resume the aging clock with: order_offhold_date

So it would be something like #DateDiff("d", thequery.order_create_date, nowtime)# minus #DateDiff("d",thequery.order_onhold_date,thequery.order_offhold_date)#

?

Any help would be appreciated- Thanks

That's correct, only you will need to take into account whether or not the order was placed on hold, and if so, whether or not it is still on hold.

I'm going to assume you have data integrity rules in place to prevent the on-hold dates from preceding the order date, that way you don't have to worry about it in code.

What you want to do is something like this:

<cfset nDaysOld = dateDiff('d', thequery.order_create_date, nowtime)>

<cfif thequery.order_onhold_date neq ''>

  <cfif thequery.order_offhold_date eq ''>

    <cfset nDaysOnHold = dateDiff('d', thequery.order_onhold_date, nowtime)>

  <cfelse>

    <cfset nDaysOnHold = dateDiff('d', thequery.order_onhold_date, order_offhold_date)>

  </cfif>

  <cfset nDaysOld -= nDaysOnHold>

</cfif>

Age: #nDaysOld#

Cheers

Eddie

TOPICS
Advanced techniques

Views

312

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Nov 30, 2016 0
New Here ,
Nov 30, 2016

Copy link to clipboard

Copied

Oops- arithmetic- sorry the nuns would have killed me for that

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Nov 30, 2016 0
Advocate ,
Dec 01, 2016

Copy link to clipboard

Copied

That's correct, only you will need to take into account whether or not the order was placed on hold, and if so, whether or not it is still on hold.

I'm going to assume you have data integrity rules in place to prevent the on-hold dates from preceding the order date, that way you don't have to worry about it in code.

What you want to do is something like this:

<cfset nDaysOld = dateDiff('d', thequery.order_create_date, nowtime)>

<cfif thequery.order_onhold_date neq ''>

  <cfif thequery.order_offhold_date eq ''>

    <cfset nDaysOnHold = dateDiff('d', thequery.order_onhold_date, nowtime)>

  <cfelse>

    <cfset nDaysOnHold = dateDiff('d', thequery.order_onhold_date, order_offhold_date)>

  </cfif>

  <cfset nDaysOld -= nDaysOnHold>

</cfif>

Age: #nDaysOld#

Cheers

Eddie

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 01, 2016 0