Date Field Display in Grid - TRUNC() and Timestamps

Toad Data Point
1

I am trying to adapt from Toad for Oracle to Toad Data Point, and one thing that’s been difficult for me is the handling of timestamps in the results grid. (Note: I am referring to Oracle DB connections in this post.)

If you run the statement below, it defaults the timestamp to midnight. I know that’s what the TRUNC() function does behind the scenes, but in Toad for Oracle it would remove the timestamp and leave just the date. Really in Toad for Oracle I think anytime the timestamp was midnight, it leaves it off and only displays the date.

SELECT TRUNC(SYSDATE) FROM DUAL ;

Result:
2/21/2019 12:00:00 AM

Desired Result:
2/21/2019

Question:
I would just apply a custom format and make it always display date fields without the timestamp, except I often have a mix of both date and datetime fields in the same dataset. I need to see the timestamp for some of the fields, but I want the timestamp suppressed/removed when it’s midnight (or truncated via the function). Is there a way to do this, perhaps with special syntax in the custom format setting? I’ve been unable to find anything like this by simply Googling it, so any help you can offer is much appreciated!

2

this is just a matter of setting the date format you want.

dateformat
dateformat808×512 40.3 KB

3

I am aware of that setting, but I was wondering if there is a custom format I can use to accomplish the following…

I work in healthcare IT, so for an example, the patient Date of Birth does not need a timestamp, but the Date of Admission for a patient stay in a hospital does need the timestamp. I would want the ability to TRUNC(Date_of_Birth) and suppress the 12:00:00 AM, but include the timestamp on the Date of Admission.

Would something like that be possible or do I have to settle for one or the other in all scenarios? The reason I ask is because Toad for Oracle does suppress the timestamp when it’s midnight.

4

The setting I suggested will overwrite all display of datetimes. If you want to use different formats you will need to include this in your SQL.

date%20time%20displays
date time displays.PNG765×622 27.9 KB

5

Thanks, Debbie. That helps to know there’s no way to override it except in my SQL scripts.

I appreciate your responses…I’m new to the forum, but it’s great to use a product that is so well supported by its dev team. :slight_smile: