Toad World® Forums

How to query date and time in the same data table in TDP 3.8

My current results show as Date = 5/1/2016 6:54:07 AM and Time = 5/1/2016 6:54:07 AM

This problem comes in due to default date format settings for all date columns.

The value in the field should be correct. You just have to set the format to short date or time. Are you exporting the data to Excel?

If you want just the date part in one field and just the time part in another, you can cast the field in the SQL query:

Select Cast(YourTimeStampField as Date) JustDate, Cast(YourTimeStampFiled as Time) JustTime From YourTable

You can also set you display options.

It does not allow me to do that in oracle, it has to be timestamp.


Where would I get the display options from?

Under Tools | Options | Environment | Grid, you can change the Data Type formatting to any format you like in DateTime tab which will apply to all DateTime data type in Oracle such as Date and Timestamp.

In addition to that, you can assign different formats for Date and Timestamp by adding them in custom tab shown below.

However, if both your date and time columns are defined as the same data type, we cannot format one but another.

You can try the oracle to_char function to format them in a select statement.

select to_char(col_name, 'yyyy-mm-dd') from tab_name