for the below mysqlplus query script, how to get DATE & TIME in DD-MMM-YY HH.MM.SS format for one column "evt_eventdate," only
set echo off
SET SQLFORMAT CSV
SET FEEDBACK OFF
SET ERRORLOGGING ON
set encoding UTF-8
Spool C:/Users/bhsadmin/Downloads/Daily Export/Daily SMC Events.csv
SELECT /*csv*/
evt_eventdate,
itemtype,
FROM
all_events
WHERE
all_eventdate BETWEEN trunc(sysdate - 1, 'DD-MON-YYYY HH24:MI:SS') AND trunc(sysdate, 'DD-MON-YYYY HH24:MI:SS')
AND lan_language_id = 1 ORDER BY evt_event_id;
Spool off
exit
for the above i get this result enter image description here
when i run the sqlscript to store all data in CSV file, for the column evt_eventdate i get only DD-MM-YY. instead i need to get the date and time in the format DD-MMM-YY HH.MM.SS to be stored in CSV.
I tried to add below, but did not work alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
Below is the expected output
CodePudding user response:
SELECT /*csv*/
to_char(evt_eventdate,'DD-MON-YYYY HH24:MI:SS')
...