Hello,
I am having a time tring to get this to work for some reason. I am trying to get all the records that are in a range of dates. (Start_Date and End_Date. For example: In a record I might have a Start date as 04/07/2023 and an end date of 04/11/2023. In my variable I have a date picker that has selected 04/09/2023. I want to be able to get all the records that the variable date would fall in to that range of dates. but i only want the records that would include the 04/09/2023 date only.
In the database the fields START_DATE AND END_DATE are setup as dates.
SELECT A.* FROM (
SELECT R.ID TRIP_NUM, R.STATUS
,TO_DATE(R.START_DATE, 'MM/DD/YYYY HH:MI PM') AS START_DATE
,TO_DATE(R.END_DATE, 'MM/DD/YYYY HH:MI PM') AS END_DATE,
R.NUM_STU, R.PROGRAM_NAME, R.ACCOUNT, R.SCHOOL, R.DESTINATION_ADDRESS, R.SPECIAL_REQ, R.REQUEST_DESC
,T.BUS_NUM, T.BUS_ATTRIBUTES
,TO_DATE(R.START_DATE, 'MM/DD/YYYY') N_START_DT
,TO_DATE(R.END_DATE, 'MM/DD/YYYY') N_END_DT
FROM MAINDB R
LEFT JOIN TICKETS T
ON R.ID = T.REQ_ID
AND T.BUS_NUM IS NOT NULL
) A
WHERE A.STATUS = 'Approved'
AND TO_DATE(:P1_SEL_DATE, 'MM/DD/YYYY')
BETWEEN A.N_START_DT AND A.N_END_DT