[Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/or time from character string.

[former_member539471]

Hello All,

I am getting this error when I try to use @prompt in derived table. I am not sure if it is a bug since a bit of search led me to few discussions that it might be.

In any case....using @prompt(param) did not solve my problem. The object in backend(DB)is INT and i defined the object as numeric in Universe.I am using BI 4.0. The prompts do work independently .Only when included in derived table I have a problem.Also this query gives results in SQL server.I just replaced with @prompt(new_param). where the parameter is select distinct values for rpt_year_month.

[former_member190895 · Accepted solution]

it seems your conversion is not working properly. First try to convert the string to datetime in SQL server.

[former_member539471 · Accepted solution]

Thanks Mantu....

Now i created a new view in SQL server and rpt_year_month is datetime. I am still getting @prompt error in derived table.My prompt definition is correct....i created a seperate filter using it and it works.

SELECT a.group_name, a.rpt_year_month, b.rpt_year_month, a.primed_status as curr_prim_status, b.Primed_Status as prev_prim_status, a.EHR as curr_EHR, b.EHR as prev_EHR,a.PATIENT_PORTAL as curr_patient_portal,

b.PATIENT_PORTAL as prev_patient_portal, a.TBC_STAFF_RATIO as curr_tbc_staff_ratio, b.TBC_STAFF_RATIO as prev_tbc_staff_ratio,a.TBC_DOC as current_tbc_stad_doc, b.TBC_DOC as prev_tbc_std_doc,

a.TBC_AHRQ as curr_ahrq_culture_survey, b.TBC_AHRQ as prev_ahrq_culture_survey, a.PR_PROV as curr_practice_redesign_prov , b.PR_PROV as prev_Practice_redesign_prov, a.PR_STAFF as curr_PR_staff,

b.PR_STAFF as prev_PR_STAFF, a. PR_LEAD_TIME as curr_PR_Leadtime , b.PR_LEAD_TIME as prev_PR_Leadtime, a.CM_CARE_MGR as curr_caremgr_staffing_ratio, b. CM_CARE_MGR as prev_caremgr_staffing_ratio,

a.CM_HIGH_RISK as curr_care_manager_HR_pts , b.CM_HIGH_RISK as prev_care_manager_HR_pts,a. HR_PRIORITIZED as curr_HR_prioritized, b.HR_PRIORITIZED as prev_HR_prioritized, a.HR_ASSESSED as curr_HR_assessed,

b.HR_ASSESSED as prev_HR_assessed,a.HR_CARE_PLAN as curr_HR_care_plan_com,b.HR_CARE_PLAN as prev_HR_care_plan_com, a.NCQA_MP as curr_NCQA_MP , b.NCQA_MP as prev_NCQA_MP, a.NCQA as curr_ncqa, b.NCQA as prev_ncqa

from         (select practice_id, group_name, primed_status, ehr, report_period, RPT_YEAR_MONTH, PATIENT_PORTAL,TBC_STAFF_RATIO, TBC_DOC,TBC_AHRQ, PR_PROV,PR_STAFF,PR_LEAD_TIME,CM_CARE_MGR,CM_HIGH_RISK,

                HR_PRIORITIZED,HR_ASSESSED,HR_CARE_PLAN,NCQA_MP, NCQA

                from EMR_REPORTING."PARTNERS\htp0".vmh_practice_variance

                where EMR_REPORTING."PARTNERS\htp0".vmh_practice_variance.Report_Period = @Prompt('Enter values for Rpt Year Month:','D','Folder\practice varience\Report Period',Mono,Constrained)) a inner join

                                                  (select practice_id, group_name, primed_status,                           

                                                    ehr,report_period,RPT_YEAR_MONTH ,PATIENT_PORTAL,TBC_STAFF_RATIO ,TBC_DOC ,TBC_AHRQ,   

                                                    PR_PROV ,PR_STAFF,PR_LEAD_TIME,CM_CARE_MGR,CM_HIGH_RISK,HR_PRIORITIZED,HR_ASSESSED,HR_CARE_PLAN,NCQA_MP,NCQA

                                                    from EMR_REPORTING."PARTNERS\htp0".vmh_practice_variance

                                                    where EMR_REPORTING."PARTNERS\htp0".vmh_practice_variance.Report_Period = dateadd(mm, -3, @Prompt('Enter values for Rpt Year Month:','D','Folder\practice varience\Report Period',Mono,Constrained)) ) b

                                         on a.Group_Name = b.Group_Name

[former_member193452 · Accepted solution]

Hi Pali,

The Data Foundation where the derived table is located cannot point to a list of values in the Business Layer.  Make sure the LOV is pointing to the data foundation object only.

https://service.sap.com/sap/support/notes/1888841

Modify the derived table and keep the 3rd parameter of @Prompt blank.Example- Select Emp.Ename from Emp where Emp.Ename = @Prompt('Enter Name','A',,mono,free)Note: with the above change no LOV's will be shown to the user. The user will have to provide the value manually.

Or

Define a new List of Value (static or custom SQL) at the DFX level. Create a new parameter which uses the List of Value created in step 1. Now, modify the derived table and use the Paramater created in step 2 instead of the previous @prompt syntax Example- Modify the Derived table as follows- Select Emp.Ename from Emp where Emp.Ename = @Prompt(Name_param) Here we created a new parameter "Name_param" which uses a List of value to bringing all the values for Ename.

[former_member539471 · Accepted solution]

Hello Jacqueline,

I am aware of this Note but the problem is parameters is not working for me as mensioned in the note ...a workaround.I am able to parse the derived table if i remove Lov's though.