Re: SQL - Date Math question

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