Home -> Usenet -> comp.databases.oracle -> SQL - Date Math question

SQL - Date Math question

From: Alex <oradeveloper_at_yahoo.com>
Date: 8 Mar 2005 05:34:14 -0800
Message-ID: <1188063f.0503080534.2f9d4ec7_at_posting.google.com> 





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.
Received on Tue Mar 08 2005 - 14:34:14 CET












Original text of this message