in some part of my program , I want to run a sql query and have the result which is a date like : %Y/%m/%d %H:%M:%S
SELECT MAX(created_at)
FROM HOT_FILES_LOGS
WHERE FILE_NAME = 'test'
date in created_at column is stored like 04/03/2021 15:45:30 ( it is fulled with SYSDATE) but when I run this query, I get just 04.03.21
what should I do to fix it?
Apply TO_CHAR with appropriate format mask:
select to_char(max(created_at), 'yyyy.mm.dd hh24:mi:ss') as created_at
from hot_files_logs
where file_name = 'test'
Oracle does not store dates or timestamps in any display format, they are stored in an internal structure, every date in every Oracle database since at least 8i and probably earlier. This structure consists of 7 1-byte integers (timestamps in a similar but larger structure). How the date is displayed or a string converted to a date is controlled the specified date format string in the to_char or to_date function or if no format string given by the NLS_DISPLAY_FORMAT setting. To get a gimps at the internal settings run the following:
create table td( d date);
insert into td(d) values(sysdate);
select d "The Date" , dump(d) "Stored As" from td;
See example. The last used format is not practical but strictly demonstrable. Well I guess you could use it to seed a repeatable random sequence.
