How To Add Last Second Into Date?

is it possible to add such a thing as last second of the day into date? Let's say that I have dates with different times and I need every date set to time 23:59:59... Is it possibl

Solution 1:

In case the solution proposed by @a_horse_with_no_name proves to be slow, it should be possible to do it this way:

+1 day -1 second is the logic I'd follow to get that result, without string concatenation:

SELECT trunc(SYSDATE) +1- (INTERVAL'1'SECOND) FROM DUAL

SQL fiddle

Translated into UPDATE

UPDATE MY_TABLE 
SET MY_DATE_COLUMN = trunc(MY_DATE_COLUMN) +1- (INTERVAL'1'SECOND) 

However Keep in mind that maintainability is of key importance regarding writing software, and reading this is much harder than the other solution proposed.

Recommended Reading

• TRUNC
• INTERVAL

Solution 2:

update the_table
   set the_date_column = to_date(to_char(the_date_column, 'yyyy-mm-dd')||' 23:59:59', 'yyyy-mm-dd hh24:mi:ss');

Solution 3:

Yes. The data column can store date values including the timestamp with second values. Use it like this -

insertinto tab_name
(dt)
values
(to_date('20/09/2013 17:50:50', 'DD/MM/YYYY HH24:MI:SS'));

Solution 4:

If it's an Oracle DATE datatype, you can take advantage of the following equivalences:

One second = 0.00001

One day minus one second = 0.99999

e.g.

UPDATE mytable
SET mydatecolumn = TRUNC(mydatecolumn)+0.99999WHERE ...;

As for readability, once everyone has memorized just these two numbers, it becomes second nature to read something like dt+0.99999 as "1 day less 1 second". YMMV.