Missing TRUNC(TIMESTAMP, <seconds> functionality
Jeffrey Jacobs, October 21, 2016 - 5:29 pm UTC
The underlying implementation is not a good reason for inconsistent/incomplete functionality. Hard to explain this to average developer, when we have TRUNC for both timestamp and date for other granularities; 'SS' should be added.
Personally, I prefer CAST(<my_ts> to TIMESTAMP(0)).
To confuse things a little, look at
https://docs.oracle.com/cd/E29805_01/server.230/es_eql/src/cdfp_analytics_lang_trunc.html You have to have your magnifying glass to realize this isn't for the database :-)
October 22, 2016 - 6:05 am UTC
CAST(<my_ts> to TIMESTAMP(0)).
Nice touch.
But when you say "when we have TRUNC for both timestamp and date for other granularities".... well, we dont. We are relying on the automatic datatype conversion (something I hate, and wish we had a setting to disable it).
For example, to_date(sysdate) is a valid command *not* because to_date takes a date parameter (it doesnt), but because we silently convert the sysdate to a string, and then apply the to_date function.
cast as timestamp(0)
Vito, March 22, 2018 - 12:24 pm UTC
Note that cast(systimestamp as timestamp(0)) works more like ROUND, where's cast(systimestamp as date) works like TRUNC
March 23, 2018 - 12:31 am UTC
Thanks - I didn't know that
Agreed Connor but
Gh, March 25, 2018 - 7:35 am UTC
something I hate, and wish we had a setting to disable it).
Well something you hate, I don't mind
But hopefully never happens and fortunately Oracle is so. Otherwise developer must all be Aces. Moreover all worldwide code applications must be reviewed and redebugged and what about recode optimizer algorithms..
March 25, 2018 - 11:27 am UTC
That is why I said a "setting", not a "rule" :-)
A reader, January 08, 2020 - 10:02 am UTC
I want to remove fractional seconds including HH:MI:SS from date value.
Input : 2014-12-18 00:00:00.000000000
Output value: 2014-12-18
How the same can be achieved?
January 08, 2020 - 10:45 am UTC
As Connor shows in his original answer, casting the timestamp to a date removes the fractional seconds.
If you also want to "remove" the time component, use trunc without a format mask. Note that dates in Oracle Database always have a time.
IS THAT A BUG OR AM I DOING SOMETING WRONG ?
Kaz Klimek, April 04, 2023 - 11:54 pm UTC
Creation_date is timestamp
Start_date is date - all belong to table A,
date ineffective and date_effective are dates in separate table (B)
I want to select records
where greatest of creation_date or start_date are between date effective and date ineffective
However formula:
GREATEST(a.start_date, CAST(creation_date AS date)) BETWEEN
b.efective_date AND b.date_ineffective -returns no rows
In order to present data, I've changed formula to CAST(creation_date+1 AS DATE), then is working:
START_DATE CREATION_DATE DATE_EFFECTIVE DATE_INEFFECTIVE
2021-02-22T07:58:18.000+00:00
2021-03-30T07:33:35.062+00:00
2021-03-30T07:33:35.000+00:00
2021-11-22T15:51:30.000+00:00
April 06, 2023 - 6:59 am UTC
Show us your full test case, ie
create table...
insert statements
your query and the output