I am getting Invalid month in this procedure if i take 8 days date range its working in case if i take more than 8 days range am getting invalid month in oracle.
i found one thing in that query if i remove TO_DATE function in the select query able to reterive the data in code level
could you please help me in that?
SELECT
ses.session_id,
( ss.participant_status ) status,
nvl(
tf.lesson_objective,
ses.lesson_objective
) lesson_objective,
ses.question,
TO_DATE(TO_CHAR(
new_time(
ses.scheduled_starttime,
'GMT',
'EST'
),
'MM/DD/YYYY hh:mi:ss am'
) ) scheduled_starttime,
DECODE(
ss.participant_status,
0,
'Created',
1,
'On-Goining',
2,
'Completed',
3,
'Student Canceled',
4,
'Student Missed',
5,
'Tutor Missed',
6,
'Admin Delete',
7,
'Admin Cancel',
9,
'Orientation Completed',
10,
'Completed - Unsuccessful',
'NULL'
) session_status,
DECODE(
nvl(
ses.session_type,
0
),
1,
'Group',
'Single'
) session_type,
DECODE(
ses.type,
0,
'On-Demand',
1,
'Admin Schedule',
2,
'Student SCHEDULED',
'NA'
) type,
ses.subject_id,
ses.subject_name,
stugrade.grade_name AS studentgrade,
ss.student_id,
st.referrence_id AS studentreferenceid,
st.first_name AS studentfirstname,
st.middle_name AS studentmiddlename,
st.last_name AS studentlastname,
stul.user_name AS studentusername,
ses.actual_endtime,
tut.tutor_id,
tut.first_name AS tutorfirstname,
tut.last_name AS tutorlastname,
tut.tutoring_center,
tutl.user_name AS tutorusername,
ses.fqst_actual_time,
ses.school_id,
sc.name AS schoolname,
ss.student_wait_time,
ses.tutor_activity_avg_res_time,
ses.session_question_loaded_time,
sf.session_rating,
sf.tutor_rating,
di.district_id,
di.district_name,
nvl(
ses.actual_starttime,
ses.scheduled_starttime
) AS starttime,
nvl(
ses.actual_endtime,
ses.scheduled_endtime
) AS endtime,
ss.joined_time AS studentjointime,
ss.quit_time AS studentquittime,
nvl(
sc.is_test_data,
0
) AS istestsession,
tf.participation_points,
tf.exit_ticket_attempted,
tf.exit_ticket_score,
tf.exit_ticket_earned,
tf.progress_notes,
tf.tutor_comments,
round(
(nvl(
ses.actual_endtime,
ses.scheduled_endtime
) - nvl(
ses.actual_starttime,
ses.scheduled_starttime
) ) * 24 * 60,
4
) AS actualduration,
round(
(ses.scheduled_endtime - ses.scheduled_starttime) * 24 * 60,
4
) AS scheduleduration,
ses.billable_duration,
ses.recording_url,
ses.chat_script_url,
( fpg.name ) program_name,
pgv.version_name,
( sf.comments ) student_comments
FROM
fev_oltsession ses
LEFT OUTER JOIN fev_student_oltsession ss ON ses.session_id = ss.session_id
LEFT OUTER JOIN fev_school sc ON ses.school_id = sc.school_id
LEFT OUTER JOIN fev_district di ON sc.district_id = di.district_id
LEFT OUTER JOIN fev_oltsession_tutor_feedback tf ON (
ses.session_id = tf.session_id
AND
ss.student_id = tf.student_id
)
LEFT OUTER JOIN fev_oltsesson_student_feedback sf ON (
ses.session_id = sf.session_id
AND
ss.student_id = sf.student_id
)
LEFT OUTER JOIN fev_student st ON ss.student_id = st.student_id
LEFT OUTER JOIN fev_login stul ON st.login_id = stul.login_id
LEFT OUTER JOIN fev_tutor_oltsession ts ON (
ses.session_id = ts.session_id
AND
ts.transfered_from IS NULL
)
LEFT OUTER JOIN fev_tutor tut ON ts.tutor_id = tut.tutor_id
LEFT OUTER JOIN fev_login tutl ON tut.login_id = tutl.login_id
LEFT OUTER JOIN fev_grade stugrade ON st.grade_id = stugrade.grade_id
LEFT OUTER JOIN fev_program fpg ON (
fpg.program_id = ses.program_id
)
LEFT OUTER JOIN fev_program_version pgv ON (
pgv.version_id = ses.version_id
)
WHERE
ses.scheduled_starttime >= TO_DATE(
'04/10/2022 4:00:00 AM',
'mm/dd/yyyy hh:mi:ss am'
)
AND
ses.scheduled_endtime < TO_DATE(
'04/18/2022 4:00:00 AM',
'mm/dd/yyyy hh:mi:ss am'
)
AND
nvl(
sc.is_test_data,
0
) != 1
AND
ses.school_id NOT IN (
213,358
)
AND
ses.status IS NOT NULL;