Using a variable with dateAdd

Sure. Just get rid of the pound signs around the offsetHour variable:

<!--- demo query --->
<cfset qry = queryNew("")>
<cfset queryAddColumn(qry, "offsetHour", "integer", [50,16,240])>
<cfset queryAddColumn(qry, "enterDate", "date", [ now(), now(), now()])>

<!--- sample code --->
<cfoutput query="qry">
	#timeformat(dateadd('n', -offSetHour, enterDate),'hh:mm:ss tt')#<br>
</cfoutput>

Select allOpen in new window

Hi agx-

Thanks for responding.  To give you full context of what I am trying to do, I've pasted my code below....Each record doesn't have an offset hour associated with it.  In those cases, I would want to display the enterDate.  The code seems to be running without error, but I am not seeing the time change when it hits <td>#timeformat(dateadd('h', -offsetHour, enterDate),'hh:mm:ss tt')#</td>

<cfquery name="getProspectInfo" datasource="kc_nick">
	select p.id, p.enterDate, p.fname,p.lname, LEFT(p.phone1,3) as prospectareaCode, p.phone1, a.timeZoneID, t.offSetHour
    from Prospects p
    left join areaCodeToTimeZones a on a.areaCode = cast (LEFT(p.phone1,3) as int)
    left join timeZoneOffSet t on t.timeZoneID = a.timeZoneID
    where Phone1 is NOT null
    and Phone1  not like '%Ex%'
    and EnterDate > '2016-06-01'
</cfquery>
<!---
<cfdump var="#getProspectInfo#"><cfabort>
--->
<cfloop query="getprospectInfo">
	<cfoutput>
        <table border="1" cellpadding="3">
        
        <tr>
        <td>#id#</td>
        <td>#enterDate#</td>
        <cfif isDefined("offsethour") and isnumeric("offsethour")>
            <td>#timeformat(dateadd('h', -offsetHour, enterDate),'hh:mm:ss tt')#</td>
         <cfelse>
            <td>#enterDate#</td>
        </cfif>
        
        <td>#fname#</td>
        <td>#lname#</td>
        <td>#prospectareaCode#</td>
        <td>#phone1#</td>
        <td>#offsethour#</td>
        
        </tr>
        </table>

	</cfoutput>
</cfloop>

Select allOpen in new window

Agx-

Think I have it..

 <cfif isdefined("offsethour") and offsethour neq ''>
            <td>#timeformat(dateadd('h', -offsetHour, enterDate),'hh:mm:ss tt')#</td>
            <td>1</td>
         <cfelse>
            <td>#enterDate#</td>
            <td>2</td>
        </cfif>

Unless you have a better method?

Thank you for the help!  Went with isnumeric(offsethour)

Thanks again

EDIT:
Oops, our posts clashed.   I was going to say I'd stick with IsNumeric anyway, since it's more accurate :)

Side note, IF you wanted to display a "time" in both cases, a semi-lazy "cheat" is to skip the IsNumeric check and use VAL(offsetHour). It converts any non-numeric values to 0. So the code becomes just:

        #timeformat(dateadd('h', -val(offsetHour), enterDate),'hh:mm:ss tt')#

That's slightly more readable IMO. Granted it does do a little more work than needed, but unless you're outputting thousands of records, any differences are likely negligible.

I switched it to Val thanks...

One last question, so my end goal is to sort my output by the new offsethour that is being displayed. Can you suggest the best way to tackle that?  I can't do the dateAdd within the query with the offsethour to give me what I need, but now trying to determine best way to order the times when I display them after my offsethour has been set?

Thanks

Any way you can modify the query? Because that's going to be the most efficient method.  Otherwise, you're stuck with a very ugly hack using a QoQ.

To show you what I mean, in a db query, you just need to add an ORDER BY clause. For example, in SQL Server:

SELECT	* 
FROM	someTable
ORDER BY CASE WHEN IsNumeric(offsetHour) = 1 THEN dateAdd(hh, -offsetHour, enterDate)
		 ELSE enterDate
		 END

Select allOpen in new window

However, if you can only use CF code it requires more convoluted code. Since QoQ's are so rudimentary (no date function support) you have to do some hoop jumping first:

<!--- DEMO: Simulate original db query --->
<cfset qryOriginal = queryNew("")>
<cfset queryAddColumn(qryOriginal, "offsetHour", [50,185,450,240, 90])>
<cfset queryAddColumn(qryOriginal, "enterDate", "timestamp", [ now(), now(), now(), now(), now()])>

<!--- 1) Add new column --->
<cfset queryAddColumn(qryOriginal, "newEnterDate", "timestamp", [])>

<!--- 2) Calculate new date and time for each record --->
<cfloop query="#qryOriginal#">
	<cfset qryOriginal["newEnterDate"][currentRow] = dateadd('h', -val(offsetHour), enterDate)>
</cfloop>

<!--- 3) Finally RE-sort results by new date --->
<cfquery name="qryNewResults" dbtype="query">
	SELECT *
	FROM   qryOriginal
	ORDER BY newEnterDate
</cfquery>

<cfdump var="#qryNewResults#" label="Final Results">

Select allOpen in new window

Thank you, I will try it out

Thanks

I don't think I can do it in the query because in order to get the proper offsethour for each record, I have to grab the area code from table a and join it to table b and join table b to table c where the offsethour resides.  
Not sure if that makes sense.

Well, unless there's more to it, since the query already includes those JOIN's, it should just be a matter of adding an ORDER BY clause. Something like:

SELECT ...
FROM ....
WHERE ....
ORDER BY CASE WHEN IsNumeric(t.offSetHour) = 1 THEN dateAdd(hh, -t.offSetHour, p.enterDate)
		 ELSE p.enterDate
		 END 

Select allOpen in new window

Thanks, I get what your saying.  It seems to be just about there...I tried adding Desc order, but it failed.  Is there a way for me to order it in desc so I can get the latest time listed first?

select p.id, p.enterDate, p.fname,p.lname, LEFT(p.phone1,3) as prospectareaCode, p.phone1, a.timeZoneID, t.offSetHour
    from Prospects p
    left join areaCodeToTimeZones a on a.areaCode = cast (LEFT(p.phone1,3) as int)
    left join timeZoneOffSet t on t.timeZoneID = a.timeZoneID
    where Phone1 is NOT null
    and Phone1  not like '%Ex%'
    and EnterDate > '2016-06-01'
    ORDER BY CASE WHEN IsNumeric(t.offSetHour) 1 THEN dateAdd(hh, -t.offSetHour, p.enterDate)
		 ELSE p.enterDate
		 END 

Select allOpen in new window

Or is there a way to take t.offSetHour and subtract it from p.enterDate in the query itself?  If so, i would be able to order it by p.enterDate.  

select p.id, p.enterDate, p.fname,p.lname, LEFT(p.phone1,3) as prospectareaCode, p.phone1, a.timeZoneID, t.offSetHour
    from Prospects p
    left join areaCodeToTimeZones a on a.areaCode = cast (LEFT(p.phone1,3) as int)
    left join timeZoneOffSet t on t.timeZoneID = a.timeZoneID
    where Phone1 is NOT null
    and Phone1  not like '%Ex%'
    and EnterDate > '2016-06-01'
    ORDER BY CASE WHEN IsNumeric(t.offSetHour) 1 THEN dateAdd(hh, -t.offSetHour, p.enterDate)
             ELSE p.enterDate
             END

agx-

Thanks again for all the help with this.  I ended up using this..

<cfquery name="getProspectInfo" datasource="kc_nick">
      select p.id, p.enterDate, p.fname,p.lname, LEFT(p.phone1,3) as prospectareaCode, p.phone1, a.timeZoneID, t.offSetHour, dateadd(hour,-t.offSetHour,p.enterDate) as offsetDate
    from Prospects p
    left join areaCodeToTimeZones a on a.areaCode = cast (LEFT(p.phone1,3) as int)
    left join timeZoneOffSet t on t.timeZoneID = a.timeZoneID
    where Phone1 is NOT null
    and Phone1  not like '%Ex%'
    and EnterDate > '2016-06-01'
       order by offsetDate DESC
</cfquery>

Sorry-  Just to close the loop, I went with the following instead because there were some instances where I had empty strings so I put the case in the select

<cfquery name="getProspectInfo" datasource="mydb">
	select p.id, p.enterDate, p.fname,p.lname, LEFT(p.phone1,3) as prospectareaCode, p.phone1, a.timeZoneID, t.offSetHour, case when isNumeric(t.offsetHour) >0 then dateadd(hour,-t.offSetHour,p.enterDate) 
    	else p.enterDate end as offsetDate 
    from Prospects p
    left join areaCodeToTimeZones a on a.areaCode = cast (LEFT(p.phone1,3) as int)
    left join timeZoneOffSet t on t.timeZoneID = a.timeZoneID
    where Phone1 is NOT null
    and Phone1  not like '%Ex%'
    and EnterDate > '2016-06-01'
 	order by offsetDate desc
    
   
</cfquery>

Select allOpen in new window

agx-

Came across an error this morning "Conversion failed when converting date and/or time from character string."

Is there a way for me to cast or convert

case when isNumeric(t.offsetHour) >0 then dateadd(hour,-t.offSetHour,p.enterDate)
          else p.enterDate end as offsetDate

EDIT:  Fix typo
That should not happen unless maybe enterDate isn't a datetime column and contains some invalid values.  For example, trying to use it on a string like this would cause that error:

           SELECT dateadd(hour,-12, 'abc')

What are the data types of those 2 columns?  I was assuming offSetHour was type INTEGER and enterDate was either DATETIME or possibly DATE.  If not, try adding an additional IsDate() check. Though that's just a band-aid.  Storing numbers/dates as strings is asking for trouble.  The better solution is to change the column(s) to use the correct data types.


           CASE
                 WHEN isNumeric(t.offsetHour) = 1 AND isDate(p.enterDate) = 1 THEN dateadd(hour,-t.offSetHour,p.enterDate)
                 ELSE p.enterDate
           END