From: fvanbortel@netscape.net   
      
   Alex wrote:   
   > I have two SQL statements:   
   >   
   > Statement #1   
   >   
   > SELECT	CARRIER_REFERENCE_NUMBER,   
   >         CURRENT_FLEET_SIZE,   
   > 	trunc(Fleet_Size_Effective_From_Date),   
   > 	trunc(Fleet_Size_Effective_To_Date)   
   > 	FROM CAPS.CARRIER_FLEET_SIZE_HISTORY	   
   > 	WHERE 	CARRIER_REFERENCE_NUMBER = 481   
   > 	AND	trunc(Fleet_Size_Effective_TO_Date) >=   
   > TO_DATE('24-FEB-2003')		AND	trunc(Fleet_Size_Effective_From_Date) <=   
   > TO_DATE('24-FEB-2004')   
   > 	ORDER BY CARRIER_REFERENCE_NUMBER,   
   >                  Fleet_Size_Effective_From_Date,	   
   > 		 Fleet_Size_Effective_To_Date,   
   > 		 CURRENT_FLEET_SIZE   
   > 						   
   > **************************************************************   
   ***************   
   > Statement #2   
   > 						   
   > SELECT	CARRIER_REFERENCE_NUMBER,   
   > 	CURRENT_FLEET_SIZE,   
   > 	TRUNC(Fleet_Size_Effective_From_Date) AS START_DATE,   
   > 	TRUNC(Fleet_Size_Effective_To_Date) AS END_DATE   
   > 	FROM CAPS.CARRIER_FLEET_SIZE_HISTORY	   
   > 	WHERE 	CARRIER_REFERENCE_NUMBER = 481   
   > 	AND TO_DATE(Fleet_Size_Effective_TO_Date,'DD-MON-YYYY') >=   
   > TO_DATE(ADD_MONTHS(SYSDATE, -12),'DD-MON-YYYY')   
   > AND TRUNC(Fleet_Size_Effective_From_Date) <=   
   > TO_CHAR(SYSDATE,'DD-MON-YYYY')   
   > 	ORDER BY CARRIER_REFERENCE_NUMBER,   
   > 		 Fleet_Size_Effective_From_Date,	   
   > 		 Fleet_Size_Effective_To_Date,   
   > 		 CURRENT_FLEET_SIZE   
   >   
   > Statement #1 returns 13 records....statement #2 returns 5 records.   
   >   
   > Both Fleet_Size_Effective_TO_Date and Fleet_Size_Effective_From_Date   
   > are of a DATE datatype.   I am trying to restrict the query to the   
   > sysdate as an end date, and sysdate - 12 months as a start date.   
   > What's wrong with my date math?   
   > Any input into this would be greatly appreciated.   
      
   Sysdate is today - not Feb, 24!   
   Also, the second query compares dates with characters (you   
   do use TO_CHAR(SYSDATE...)) and may thus not perform as well   
   --   
   Regards,   
   Frank van Bortel   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)