Hi I am pulling data from sql server to frame work manager query subject.
I have 2 date columns in my table Admit Date and Drug Started date.
Admit Date format = 2017-01-05
Drug started date format = Jan 18, 2017 9:35:00 AM
so I used following function to convert Drug Started date into 2017-01-18
cast ( Drug started date AS nvarchar(4)) || '-' || substring (cast ( Drug started date AS nvarchar(7)),6,2)
|| '-' || substring (cast ( Drug started date AS nvarchar(14)),9,2)
now I am trying use _add_days function to get the drugs that with in 4 days of admission
[Drug Started date] between [Admit Date] and _add_days([Admit Date],3)
but here I am getting below error:
RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-126'.
UDA-SQL-0460 A general exception has occurred during local processing.
UDA-EE-0094 The operation "less_equal" is invalid for the following combination of data types: "nvarchar" and "date2"
please anyone can help me how to resolve this issue.
My first step would be to try to figure out what the error message is telling me.
In your case, it is saying something about an operation being invalid with the combination of nvarchar and date2 data types.
I'm guessing that the nvarchar thing is your expression in which you are extracting bits of drug started and concatenating them into a string. By the process of elimination that means admit date is probably the date data type.
What's the original data type of drug started? Is it a timestamp? Instead of your complex expression why don't you just cast it as date? If you've tried that and it didn't work, what error did you get?
There is a db2 function called date, which takes a string in the format that you've created and casts it as a date. I don't recall off the top of my head what other vendors have available. No matter what, it would be simpler to use the cast function and save processing time.