Home > Enterprise >  for the below sqlplus query script, how to get DATE & TIME in DD-MMM-YY HH.MM.SS format for one colu
for the below sqlplus query script, how to get DATE & TIME in DD-MMM-YY HH.MM.SS format for one colu

Time:01-01

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

enter image description here

CodePudding user response:

SELECT /*csv*/ 
    to_char(evt_eventdate,'DD-MON-YYYY HH24:MI:SS')
...
  • Related