Hi,
In SQL, I can't able check with Date and Time attribute, even i tried with #2019-01-01 00:00:00# then also i got this error, can anyone please tell what is the actual problem i did.
Thanks in Advance
Thanigai Arasu wrote:
Bernardo Condé wrote:
Hello,
In your test you are using double quotation marks (") directly in sql, it may be that you are getting in the way, try putting in single quotes (').
I hope to help.
Hi Bernardo Condé,
Thanks for your reply, i tried with single quote also but its gives below issue.
Do you need to use the time?
If not, you can run a test using: TRUNC ({Products}. [AprovaDTTM] = '2019-01-01'
If this is even your date format.
TRUNC ({Products}. [AprovaDTTM] = '2019-01-01' - Can you send the clear syntax. because date is enough for me.
I made it for you to see, and it worked perfectly.
Hi Thanigai,
In SQL, the string delimiter is the single quote. You used double quotes around the date/time.
Kilian Hekhuis wrote:
Hi Kilian Hekhuis,
Thanks for your quick reply, even I've used single quotes also but it gives below error which i've attached here.
Thank you Bernardo Condé,
Its working, super......
I hope I have helped friend. I did my best to be the clearest, and as you could see, what I said worked perfectly. I'm sorry if I messed up.
Thank you Bernardo Condé.
You're welcome friend.
Bernardo,
I already said that :). Also, the # cannot be used in SQL!
Hi there Thanigai.
You can use the "cast" in your sql.
Ex.:
SyntaxEditor Code Snippet
WHERE {TABLE}.[DATA] = CAST('2019-01-01 00:00:00' AS DATETIME)
Regards.
Carlos Alfradique
Carlos Alfradique wrote:
Hi Carlos Alfradique,
Thanks for your reply, i've tried this but still i have issue, that i've attached below.
Hi Thanigai.
You already validate the dataType of your attribute (ApprovalDTTM)?
This Attribute is a DateTime or only Date?
If is Date, change in Cast 'As datetime' to 'as date'.
Regards,
That approvalDTTM's DataType is DateTime only.
You are using an oracle database. please check the syntax of converting a textdate to an oracle date-datatype.
e.g. use TO_DATE('2019/01/01', 'YYYY/MM/DD') instead of the CAST function.
Hans
The error shows that the database expects the month to come first (instead of the year). Please follow Hans's advise.
That said, the alternative is to add a DateTime parameter to the advanced query, and set it's value the OutSystems way (so #2019-01-01 00:00:00#).
EDIT: Also, the query as it is currently defintely should not be an SQL query! Why don't you use an Aggregate?
I need to create dynamic query that's why i'm going with SQL. So here i pass the single parameter that having a full query.In this query i'm having datetime condition in where condition.
Hi Guys,
Thanks for your responds, I've tried all the thinks which said above, but still i got the issue, i can't able to do in sql, but i need to do with sql only because i'm going to build a dynamic query, so now i need the clarify, even i can't able to fetch simple query by using datetime. i've attached the simple query below, can anyone please execute the query.
Have you tried what Hans said? I.e. use TO_DATE('2019/01/01', 'YYYY/MM/DD') (or an equivalent that also includes the time)? If so, what was the error? Also, why not use an input parameter like I suggested?
I've tried this Kilian Hekhuis, but its not working.
Again, what's the error message?
Yes, the Bernardo's last post indeed worked, but his first post didn't include the TO_DATE that Hans already mentioned. Also, you actually can include the time in a TO_DATE expression (see e.g. here), so there's no need for a TRUNC per se.