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 ______________________________________________________________________ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm 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
