Query Help Please

[frankjshin]

Hi,
This is from a calendar app that I got from the Exchange. I am in the midst
of switching over the database from Access to Oracle. From this query, I get
an invalid column name error from Oracle. Any ideas? The field cal_date does,
in fact, exist. When I throw the query into sql plus (minus the day function),
it works fine...



<cfquery name="calappshow" datasource="#protdsn#">
SELECT #appttable#.*, (day(cal_date)) AS getthisday
FROM #appttable#
WHERE cal_date >= '#dateformat(CreateDate(Year(DateOb),Month(DateOb) ,1),
'dd-mmm-yyyy')#'
AND cal_date <=
'#dateformat(CreateDate(Year(DateOb),Month(DateOb) ,DaysInMonth(DateOb)),
'dd-mmm-yyyy')#'
</cfquery>


Thanks,
Frank

[tconley]

(day(cal_date)) as getthisday. Is day() an Oracle function? I can't find a
reference

In sqlplus does the WHERE statement work? cal_date = 'a date string' seems
like it shouldn't work.

There may be other ways but I always use the Oracle TO_DATE and TO_CHAR
functions when dealing with dates. Your query would be something like:

SELECT #apptable#.*, TO_CHAR(cal_date, 'DD') AS getthisday
FROM #apptable#
WHERE cal_date >=
TO_DATE('#dateformat(CreateDate(Year(DateOb),Month (DateOb),1),
'dd-mmm-yyyy')#', 'DD-MON-YYYY')
AND cal_date <=
TO_DATE('#dateformat(CreateDate(Year(DateOb),Month (DateOb),DaysInMonth(DateOb)),
'dd-mmm-yyyy')#', 'DD-MON-YYYY')

which should wind up being:

SELECT faketablexx.*, TO_CHAR(cal_date, 'DD') AS getthisday
FROM faketablexxx
WHERE cal_date >= TO_DATE('01-Jul-2005', 'DD-MON-YYYY')
AND cal_date <= TO_DATE('31-Jul-2005', 'DD-MON-YYYY')