[Solved] Oracle TO_DATE Error: Not a Valid Month

zeeshanpas
 
on Apr 18, 2022 05:27 AM
1008 Views

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;

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download