How to deal with 2 date fields where 2nd field goes > 1st date field. RRS feed

  • Question

  • Hi All,

    I am having problems with datefields, referring to the sample data below.

    MIF405 2014-08-20 00:00:00.000 2014-09-24 00:00:00.000 35 Aug Sep

    I am trying to get the percentage of bookings per month and i wanted to get the number of days a vehicle is booked per month.(worthy noting that there are multiple records per vehicle)

    With the data presented above, the 1st date field is on sept and the 2nd is in august, since the booking is set to be 35 days.

    Since I need to get the per month days booked, i need to cut it to stop counting til Aug only and for september will be a whole new daysCount.
    Is there a function or method to do this?

    this is my current query:

    SELECT r.VEHICLE_ID_FW,r.FROM_DATE_FW,r.TO_DATE_FW,DATEDIFF(day,r.from_date_fw,r.to_date_fw) no_of_days
    ,CONVERT(char(3),DATENAME(month,FROM_DATE_FW)) from_month,CONVERT(char(3),DATENAME(month,TO_DATE_FW)) to_month
    FROM RENTALS_FW r LEFT JOIN VEHICLES_FW v ON r.vehicle_id_fw=v.vehicle_id_fw

    Monday, November 3, 2014 7:43 AM