SQL - Date Math question

[Alex]

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.

[Frank van Bortel]

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