Thanks Allen.
that didn't work either
i am trying this
but with no success
<cfquery name="set_Emp" datasource="RPH" dbtype="ODBC">
SELECT #Year(todayDate)# - Year(HIRE_DATE) AS YearsWithFirm, <CFIF
('YEARS_OTHER_FIRMS' EQ "NULL") OR ('YEARS_OTHER_FIRMS' IS " ") OR
('YEARS_OTHER_FIRMS' IS "")>#Year(todayDate)# - Year(HIRE_DATE) AS
TotalYearsExperience,<cfelse>#Year(todayDate)# - Year(HIRE_DATE) +
YEARS_OTHER_FIRMS AS TotalYearsExperience,</cfif>
-paul
-----Original Message-----
From: Allan Pichler [mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 23, 2002 10:00 AM
To: CF-Talk
Subject: RE: Date Time Manipulations in Query?
What about this ???
SELECT #Year(todayDate)# - Year(HIRE_DATE) AS YearsWithFirm,
#Year(todayDate)# - Year(HIRE_DATE)AS TotalYearsExperience
WHERE YEARS_OTHER_FIRMS IS null
UNION
SELECT #Year(todayDate)# - Year(HIRE_DATE) AS YearsWithFirm,
#Year(todayDate)# - Year(HIRE_DATE) + YEARS_OTHER_FIRMS AS
TotalYearsExperience
WHERE YEARS_OTHER_FIRMS IS NOT null
Not sure if it helps, didn't try it out!
@llan
-----Original Message-----
From: Paul Ihrig [mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 23, 2002 3:49 PM
To: CF-Talk
Subject: RE: Date Time Manipulations in Query?
ok
this is what i got to work in my select now.
<CFSET todayDate = #DateFormat(Now(), "mm/dd/yyyy")#>
<cfquery name="set_Emp" datasource="R2D2" dbtype="ODBC">
SELECT #Year(todayDate)# - Year(HIRE_DATE) AS YearsWithFirm,
#Year(todayDate)# - Year(HIRE_DATE) + YEARS_OTHER_FIRMS AS
TotalYearsExperience,
But if the YEARS_OTHER_FIRMS in NULL/Blank or Zero
then the alias TotalYearsExperience doesnt generate.
So how can i force a null to 0 in my select.
can i use a cfif in the select part?
Thanks Again
befor i was overriding this by
cfif
hire date: #DateFormat(HIRE_DATE, "mm/dd/yyyy")#
years other firms: <CFIF #YEARS_OTHER_FIRMS# IS ""><CFSET YEARS_OTHER_FIRMS
= 0><CFELSE>#LSNumberFormat(YEARS_OTHER_FIRMS)#</cfif>
Years At Firm: #YearsAtFirm# <CFSET TotalD = #Year(todayDate)# -
#Year(set_Emp.HIRE_DATE)#> #TotalD#
Total Years Experience: <CFSET TotalYearsEx1 =
#LSNumberFormat(YEARS_OTHER_FIRMS)# + #TotalD#>#TotalYearsEx1#
-paul
______________________________________________________________________
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
