Dave, As you can probably figure out I am very new to this. I tried your query and it did give me a date of three days ago. How would I implement this in my query? -Patrick
-----Original Message----- From: Dave Carabetta [mailto:[EMAIL PROTECTED] Sent: Sunday, March 23, 2003 2:32 PM To: CF-Talk Subject: Re: Oracle Date Querry > How can I query an Oracle Date field as an int? I am trying to find > rows that are 3 days old and the date fields in Oracle are timestamps. I need to some how ignore the time in the date field in the query > > This is what I have tried: > > <cfquery name="Thirdday" datasource="badge_access_dsn"> SELECT * FROM > SPS_NON_MOT_BPS.SITES WHERE > ((int(CREATION_DATE) <= int(now())-3 AND Badging_status = 'In Approval Cycle') > OR (Badging_status = 'In Renew Cycle' AND int(MODIFY_DATE) <= int(now())-3)) > AND (SUPPLY_MGMT_APPROVAL_STATUS = '' OR M_CONTACT_MGR_APPROVAL_STATUS > = '') > </cfquery> > > I get an error of: > > [ODBC Oracle driver][Oracle]ORA-00904: invalid column name Well, the invalid column name doesn't mean that you're date conversion logic is wrong, it means that you're referring to a column that does not exist -- two different issues. Question though? Why not just use trunc(sysdate)-3? You seem to be putting a lot more effort into your date conversion than is necessary. Try running this query to see what it's doing: <cfquery name="getdate" datasource="myDSN"> SELECT trunc(sysdate)-3 FROM dual </cfquery> Regards, Dave. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
