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

Hi Thanigai,

In SQL, the string delimiter is the single quote. You used double quotes around the date/time.

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.

Bernardo,

I already said that :). Also, the # cannot be used in SQL!

Thanigai Arasu wrote:

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

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

Kilian Hekhuis wrote:

Hi Thanigai,

In SQL, the string delimiter is the single quote. You used double quotes around the date/time.

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.

Carlos Alfradique wrote:

Thanigai Arasu wrote:

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

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

Hi Carlos Alfradique,

Thanks for your reply, i've tried this but still i have issue, that i've attached below.



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.


Thanigai Arasu wrote:

Carlos Alfradique wrote:

Thanigai Arasu wrote:

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

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

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,

Carlos Alfradique


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.

Regards,

Hans

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.


Carlos Alfradique wrote:

Thanigai Arasu wrote:

Carlos Alfradique wrote:

Thanigai Arasu wrote:

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

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

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,

Carlos Alfradique


Hi Carlos Alfradique,

That approvalDTTM's DataType is DateTime  only.


Hi Thanigai,

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?

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.

Hi Thanigai,

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?

Kilian Hekhuis wrote:

Hi Thanigai,

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?


Hi Kilian Hekhuis,

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.

Bernardo Condé wrote:

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.


Hi Bernardo Condé,

TRUNC ({Products}. [AprovaDTTM] = '2019-01-01'  - Can you send the clear syntax. because date is enough for me.


Kilian Hekhuis wrote:

Hi Thanigai,

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?

Solution

Thanigai Arasu wrote:

Bernardo Condé wrote:

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.


Hi Bernardo Condé,

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.


Solution

Kilian Hekhuis wrote:

Thanigai,

Ignore Bernardo. He is not being helpful here. Instead try the syntax Hans posted.

Hi Kilian Hekhuis,Thank you so much, its working, super............


Bernardo Condé wrote:

Thanigai Arasu wrote:

Bernardo Condé wrote:

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.


Hi Bernardo Condé,

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.


Thank you Bernardo Condé,

Its working, super......


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.

Thanigai Arasu wrote:

Bernardo Condé wrote:

Thanigai Arasu wrote:

Bernardo Condé wrote:

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.


Hi Bernardo Condé,

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.


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.


Bernardo Condé wrote:

Thanigai Arasu wrote:

Bernardo Condé wrote:

Thanigai Arasu wrote:

Bernardo Condé wrote:

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.


Hi Bernardo Condé,

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.


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é.

Thanigai Arasu wrote:

Bernardo Condé wrote:

Thanigai Arasu wrote:

Bernardo Condé wrote:

Thanigai Arasu wrote:

Bernardo Condé wrote:

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.


Hi Bernardo Condé,

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.


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.