> try # 1:: > select dateDiff('d',#today#,'dte_2dlcl') as > Results:: Error - "DateDiff invalid identifier"
If you are trying to use MS SQL's function, it does not allow quotes around the datepart. So it would just be: d not 'd' (in quotes). Also if "dte_2dlcl" is a column name, remove the quotes or your database will think it is a literal string. ie dateDiff('d', today, 'dte_2dlcl') http://msdn.microsoft.com/en-us/library/ms189794.aspx ------------------------- Leigh http://cfsearching.blogspot.com/ --- On Thu, 1/13/11, Jay Birdsell <john_birds...@hotmail.com> wrote: > From: Jay Birdsell <john_birds...@hotmail.com> > Subject: help w/ DateDiff() please > To: "cf-talk" <cf-talk@houseoffusion.com> > Date: Thursday, January 13, 2011, 7:41 PM > > The dateDiff () is killing me! can someone please > look at this and help me clear my head. > > What I have is a db that tracks executive > correspondence when drafts are due and when the final > response is due. I need a report that will > list any correspondence that has a draft or final date > within 8 days of the run date (today). I've read that i > should be able to do this right in the select statement. > However I have found a clean concise example here is what I > have tried so far; > > try # 1:: > <cfquery name="final8" datasource="dev11g" > > > select dateDiff('d',#today#,'dte_2dlcl') as > m > from > execcoresp000 > </cfquery> > > Results:: Error - "DateDiff invalid identifier" > > try# 2 > > <cfquery name="final8" datasource="dev11g" > > > select dateDiff('d',#today#,'dte_2dlcl') as > m > from > execcoresp000 > where m < 8 > </cfquery> > Results: Error M invalid identifier > > try # 3 > <cfquery > name="final8" datasource="dev11g" > > select > dte_2dlcl > from > execcoresp000.correspondence > where > dateDiff('d',#today#,'dte_2dlcl')< 8 > </cfquery> > Results: Error - "DateDiff invalid identifier" > > > The other error i tend to get is "Missing right > parenthese" > > i really dont want to get in a bunch of nested loops to do > this. any ideas? > > tia, > > jbird > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340772 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm