Date parameters in Toad Data Studio - need date option without time

Toad Data Studio
1

I really like Toad a LOT other than one problem. I prompt for date parameters quite often and Toad doesn't have an option on entry of the parameter for a Date only (no time). This causes problems in the SQL in the comparison to a date (ORA-01830: date format picture ends before converting entire input string).

This same SQL works fine in a data model in Oracle or SQL Connect, but errors in Toad. I can't see modifying my code just so Toad like this when if there was an option for Date Only in the long list of parameter types, this should be resolved.

Any plans for that to happen?

2

Hi Terri,

This was posted in the Toad for Oracle forum. I will move it to the TDS forum, which is here

-John

3

Thanks John, my first time posting here!

4

Hi Terri, thanks for using our user forums.
We’ve seen a similar issue in Toad before, but we’ll need some additional information to help isolate the root cause in your case. Could you please run the attached statements and share the results with us? Based on that, we could then suggest a suitable workaround. Thanks

TDS Date parameter script.txt (1.6 KB)

5

Thanks for your help, I have attached your questions with the results imbedded.

(attachments)

Toad Data Studio script results 04242026.txt (2.19 KB)

6

Thanks Terrin, we'll have a look and get back to you.

7

I really appreciate that, a lot of the functionality is so helpful.

The next biggest problem we have run into is Toad erroring when you use in–line (--) comments, but only in some places – especially at the beginning or end of a SQL query. I am a big user of those along with the block comments (/* */) and hate to think of all the work I might need to do to modify/run any of my SQL in Toad without having to retrofit all of that….

Just fyi – my name is actually Terri – our mails here are first + last initial – thus the terrin from Terri N.

Thanks Gita!

8

Thanks Terri!
Based on the provided results our team can confirm that the issue most likey related to using a DATE bind parameter directly in Toad. For example, you mentioned this fails for you:

SELECT TRUNC(:P_AS_OF_DATE) AS test_date FROM dual;

But using a VARCHAR bind parameter and explicitly converting it with TO_DATE works correctly:

SELECT TRUNC(TO_DATE(:P_AS_OF_DATE, 'YYYY-MM-DD')) AS test_date
FROM dual;

For now, the safest and best workaround is to define the bind variable as:

Bind variable type: VARCHAR
Bind variable value: 2026-04-23

And then convert it in the SQL: TO_DATE(:as_of_date, 'YYYY-MM-DD')

So in your example: "alias.field <= :as_of_date" the temporary workaround would be: "alias.field <= TO_DATE(:as_of_date, 'YYYY-MM-DD')"

Please note that this compares against midnight at the start of the selected day. For example, 2026-04-23 is treated as 2026-04-23 00:00:00. If your date column can contain time values and you want the condition to include the entire selected day, this version is best:

alias.field < TO_DATE(:as_of_date, 'YYYY-MM-DD') + 1

For date equality filters, the safest pattern is usually: alias.field >= TO_DATE(:as_of_date, 'YYYY-MM-DD') AND alias.field < TO_DATE(:as_of_date, 'YYYY-MM-DD') + 1

This avoids relying on the current DATE bind parameter handling and should work consistently while we investigate a proper fix in Toad.

I hope this helps out but please let us know of other questions.

9

Also regarding the inline -- comment issue, we’ve seen similar scenarios where Toad may try to process colon-prefixed text inside a comment. For example:

SELECT 1 AS test_value
FROM dual; -- :IGNORED_PARAM should not be detected

Since anything after -- should be ignored as a comment, :IGNORED_PARAM shouldn’t be treated as a bind variable or script command. This could be releated to what you’re experiencing, especially if comments contain text such as:

-- :as_of_date
-- TODO: check :value
-- parameter: :report_date

Could you send us a small SQL example that fails in your environment because of an inline -- comment?

It would also help to know:

1. Whether the SQL is selected before execution, run from the cursor position, or any other specific way you're running it.
2. The exact error message.
3. Whether the comment contains any colon-prefixed text, such as :as_of_date, :value, or :report_date, or something else that could be involved.

Thanks.

10

Ok, I can switch to using VARCHAR (vs CHAR) – most of the time that works.

Any plans to add “DATE only” to the selection list though? Annoying to have to code just so that it doesn’t fight with it in Toad, but an Oracle Data Model and other products like SQL Connect allow it without a problem… It is nice to have the data picker rather than typing text (that isn’t the big deal), but also to have to wrap values just to get the SQL to not fail, when it would work in the data model I don’t find as a good long term solution….

Maybe we are unusual, but those running SQL queries and entering parameters rarely care about time, just the date….

Thanks for your quick response and help, I do appreciate it!

11

As I said, it appears to mainly be a problem (possibly always, seems a bit erratic) if it is the 1st line of the SQL or the last line of the SQL.

The SQL I am running right now, I put this comment at the top:

-- this is for invoices that are not for expense reports (those start with "ER" and have a possibility of MANY attachments)

If I select all to run or am on the 1st line when I run the SQL, it gives the following error, if I move to the line after that, it runs fine:

If the comment is in /* */ comments, it does not ever error.

Possibly it has something to do with the characters in the text also, but I can’t say that it would ever have been with a “:” for a parameter.

Another one I had to do this on was to be used in a Person Security Profile – so to run it in SQL, my last line was --) with a note above that indicating to uncomment if it was to be moved to the PSP. That errored, so I had to turn this into /)/ tog keep it from erroring, just running the SQL in Toad….

12

Hi Terri,

Yes, we are planning to fix the DATE related issue in our end of the year release. For your refence, the fix has been logged as ID # IMTDS-4354

13

Regarding the comments issue, our team have yet to reproduced the ORA-00900 error.

They did observe one related behavior - if the cursor is placed on a leading -- comment and Run Current Statement is used, Toad may not execute the following SQL statement. If the cursor is moved to the actual SQL line, the query executes successfully. They also observed that selecting a SQL block that starts with a -- comment may disable the Run option in some cases. But it's may not be the same as your ORA-00900 error.

If you get chance to provide the results of the attached document, that would help us confirm whether this is the same leading-comment execution behavior or a separate SQL parsing issue.
Thanks.

Toad Data Studio comments issue.txt (307 Bytes)

14

That is excellent to hear! Is the fix to be an additional type in the parameter choices, do you know yet – just curious!

We are considering going exclusively with your product, but that issue has kept that from happening so far.

15

Here is the feedback on the comment issue – let me know if any of it is not clear. I found a shorter SQL script that errors for you to see.

(attachments)

Toad Data Studio comments issue - answers.txt (4.86 KB)

16

And now that I sent that, I just added a – comment on a line in this SQL:

SELECT

rcpt.receipt_number,

rcpt.receipt_date,

rcpt.amount,

rcpt.currency_code,

rcpt.status,

rcpt.created_by,

rcpt.creation_date,

xah.je_category_name,

xal.entered_dr,

xal.entered_cr,

xal.accounted_dr,

xal.accounted_cr,

gcc.segment1,

gcc.segment2,

gcc.segment3,

gcc.segment4

FROM

ar_cash_receipts_all rcpt

JOIN xla_transaction_entities xte ON xte.source_id_int_1 = rcpt.cash_receipt_id

AND xte.application_id = 222

--AND xte.ledger_id = :ledger_id

JOIN xla_events xe ON xe.entity_id = xte.entity_id

JOIN xla_ae_headers xah ON xah.event_id = xe.event_id

--AND xah.ledger_id = :ledger_id

JOIN xla_ae_lines xal ON xal.ae_header_id = xah.ae_header_id

JOIN gl_code_combinations gcc ON gcc.code_combination_id = xal.code_combination_id

WHERE

--rcpt.org_id = :org_id

--AND

gcc.segment4 = '1105'

AND rcpt.created_by = 'Service.Acct' -- filter by 3rd party service account

Which works fine without the – comment I added on the last line, but gives the ORA-00907: missing right parenthesis when I include all the SQL – so that line is just text – but would be the last thing parsed…..

17

Thanks Terri! We'll have a look and get back to you.

18

Hi Terri, to help our team replicate the comments issue and narrow down what can be the root cause on their side, could you let us know if you're using Oracle Direct connection, Oracle Client, or Oracle Fusion as data source?
Is the DATE issue using the same data source as for the comments one?
Thanks.

19

Oracle Fusion – everything I am doing with SQL is from Fusion, so the same data source, yes. Two different data models, of course.

20

The latest one I sent you is for a Person Security Profile that is used in time cards – so it is the FSCM data source. I think the other one may be too – if you need me to try with an HCM let me know, but I doubt that makes a difference.

I know at least one other user here has run into this same issue, and he mainly works with HCM data – he has just given up on using “—” for comments. And he is the one that mentioned that he just doesn’t pick Date as the parameter type and types in ‘2026-01-01’ for instance.