If the requested earliest and latest dates fall within the same month,
everything run well...but, if two months are spanned, no results are returned.
Why?
Rick
Here is a query that we use to find birthdays for the next 28 days on our
intranet. This may not be the most elegant solution, but it does work. This
is against an Oracle database and uses some Oracle functions, but I presume
similar ones exist in most mainline DBMS.
<cfquery name="BDay" datasource="webLawson"
cachedwithin="#CreateTimeSpan(1,0,0,-1)#">
SELECT EMPLOYEE, FIRST_NAME, LAST_NAME, BIRTHDAY, STATUS,
TO_NUMBER(TO_CHAR(EMPLOYEE_DEPT.BIRTHDAY, 'MM')) AS Month,
TO_NUMBER(TO_CHAR(EMPLOYEE_DEPT.BIRTHDAY, 'DD')) AS Day, NAME_SUFFIX, NICKNAME
FROM LAWSON.EMPLOYEE_DEPT
<cfif Day(Now()) LT Day(DateAdd("d",28,Now()))>
WHERE (TO_NUMBER(TO_CHAR(EMPLOYEE_DEPT.BIRTHDAY, 'MM')) = #Month(Now())#
AND TO_NUMBER(TO_CHAR(EMPLOYEE_DEPT.BIRTHDAY, 'DD')) BETWEEN #Day(Now())# AND
#Day(DateAdd("d",28,Now()))#)
<cfelse>
WHERE ((TO_NUMBER(TO_CHAR(EMPLOYEE_DEPT.BIRTHDAY, 'MM')) = #Month(Now())#
AND TO_NUMBER(TO_CHAR(EMPLOYEE_DEPT.BIRTHDAY, 'DD')) >= #Day(Now())#)
OR (TO_NUMBER(TO_CHAR(EMPLOYEE_DEPT.BIRTHDAY, 'MM')) =
#Month(DateAdd("m",1,Now()))# AND TO_NUMBER(TO_CHAR(EMPLOYEE_DEPT.BIRTHDAY,
'DD')) < #Day(DateAdd("d",28,Now()))#))
</cfif>
AND FIRST_NAME NOT LIKE ('%Unknown%') AND STATUS LIKE ('A%')
<cfif Month(Now()) IS 12>
ORDER BY Month Desc, Day
<cfelse>
ORDER BY (TO_CHAR(EMPLOYEE_DEPT.BIRTHDAY, 'MMDD'))
</cfif>
</cfquery>
--------------
Ian Skinner
Web Programmer
BloodSource
www.BloodSource.org
Sacramento, CA
---------
| 1 | |
--------- Binary Soduko
| | |
---------
"C code. C code run. Run code run. Please!"
- Cynthia Dunning
Confidentiality Notice: This message including any
attachments is for the sole use of the intended
recipient(s) and may contain confidential and privileged
information. Any unauthorized review, use, disclosure or
distribution is prohibited. If you are not the
intended recipient, please contact the sender and
delete any copies of this message.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:261435
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
