Toad World® Forums

Date TIme Formatting in TDA Query Builder

I have an Oracle database and two columns (start_date_time and end_date_time)where I store the date and time in these columns. Using TOAD for Oracle when I query these columns I recieve the data and time. Using TDA Query Builder I only get the date. Can I get the date and time in the query builder or do I need to create a report.

A second question: I want to subtract the end date/time from the start date/time to tell how long the process is taking. Currently I have an excel spreadsheet that does all this processing and I am trying to mirror in TDA. Would the best way be to do the subtraction after sending the data to the excel spreadsheet? Thank you for the assistance.

There shouldn’t be any reason the time display is different between TDA and Toad for Oracle. Can you send a full screen shot of both showing the data as well as connection type and datatype?

On the second question I would subtract my dates in the SQL and then export to excel.

SELECT ORDERS.ORDER_DATE,
ORDERS.ESTIMATED_SHIP_DATE,
(ORDERS.ESTIMATED_SHIP_DATE - ORDERS.ORDER_DATE) diffDate
FROM QUEST_STAGE.ORDERS ORDERS

Debbie

Debbie I apologize for the delay. The attached Word document contains screen shots of the TOAD for Oracle query, the table layout the TDA query result and then my two “calculated” query and results. The subtraction of dates in Oracle and then trying to get them in a displayable format is not as easy as Excel formatting. I was hoping TDA would work similar to Excel in that regards.
TOAD-TDA.doc (486 KB)

select to_timestamp(order_date) from orders

Also, in the next release you will be able to set the date time formatting for the whole application and it will use this formating in the grid as well as exportig to Excel.

This will also be available in the next Beta.

Debbie

It looks like there is a bug in TDA 2.6 where Dates do not show the time portion. We have already fixed this for the next release. As a workaround you will need to cast a date to a timestamp.

WHERE (JOB.STRT_DT_TM = ‘’ /Today/

). It appears to be woring correctly but a little confusing. Is “Today” resolving to the database date?

I am looking forward to the next beta release.

Debbie - thank you. Your responses have been helpful. However I founf the to_timestamp would not read the correct time. The result would return to 12 midnight for all the records. However I could use the TO_CHAR and have no problems TO_CHAR (JOB.STRT_DT_TM, ‘MM/DD/YYYY HH:MI:SS’) AS “Start Time”,TO_CHAR (JOB.END_DT_TM, ‘MM/DD/YYYY HH:MI:SS’) AS “End Time”.

Another question along the same lines. Still in Query Builder. When I use the Today function on the where clause in the diagramming tab and switch to the Query tab the where clause loos like the following

The /Today/ is a token for some work we do under the hood. This would resolve to your system date. The other tokens produce the date range covered by its name. When you execute them you can look at the messages tab (On result tab) and see what the token was resolved to. In our next release we expose this by using a hyperlink so you can see it easier as well as copy and paste the syntax for use in other queries.

Debbie

Debbie, can you tell us what version the time portion issue is fixed in and when that is expected to release? I’m on 2.6.0.561 and don’t have the issue. Co-workers on 2.6.1 do have the issue so we are trying to decide whether to have them fall back to 2.6.0 or wait. Thanks.

It will be in TDA 2.7 which is due out at the end of Oct.

Debbie

[Milestone].[ExpectedDate] >= ‘’ /Today/

I tried to use /Today/ in my where clause to only return upcoming milestone dates

But “greater than or equal to” give me a database error:

Incorrect syntax near the keyword ‘BETWEEN’.

When I mouse over the Results error record the tooltip shows a between not a >= (see attached image).

What I am looking for, is a way of selecting upcoming milestone dates with an expected date of today onwards. That way I filter out all past dated milestones from my report.

Anyone able to suggest how I can do this?

Thank you in advance.

Regards

David
Error_ToolTip_Where_Clause.jpeg

[Milestone].[ExpectedDate] >= TRUNC(SYSDATE)

I really do not use the Query Builder. I would just write out the SQL statement and run it.

What about using

Thanks again for your help.

Regards

David

@acharest_031

Thanks for that…

TRUNC(SYSDATE) did not work for me, so I looked for a function for SQL Server and came across GETDATE()

Changed my where clause to the following and it worked

Milestone].[ExpectedDate] >= GETDATE()

Sorry, I thought you were using Oracle for the database. Glad you found the GetDate!