> 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
Get the mailserver that powers this list at http://www.coolfusion.com

                                Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
                                

Reply via email to